Question about VBA CODE not working good


  1. Posts : 399
    Windows 10 X64
       #1

    Question about VBA CODE not working good


    hi guys,

    do not know if there are VBA specialists on the board, but I pop the question:

    since recently when I save a docu automatically, like 199-invoice I get:

    blanc 199-invoice, instead of the same without the blanc position in front. Sorting the invoice at the back, since sort happens decending.

    When I remove the blanc in front the sort happens correctly, but when I rename the invoice to 199-invoic (not altering the blanc position) all of a sudden the sort is ok as well.

    Very strange.

    In order to remove all macro's and codes to get a blanc document I use:

    ActiveSheet.Copy
    With ActiveSheet.UsedRange
    .Copy
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
    End With

    This happens before the save and my guess is that the problem lies there.


    any ideas are welcome

    thanks

    jeff
      My Computer

  2. Superfly's Avatar
    Posts : 3,325
       #2

    I'm no VBA specialist but seems it's a formatting issue - if what you mean by "blanc" is a leading space?
    In normal code Trim remove those, but in VBA I suppose one of the constants does that - maybe try ..

    .PasteSpecial Paste:=xlPasteValues

    Not sure why removing he last char has a different output to the sort function though...
      My Computer


  3. Posts : 399
    Windows 10 X64
    Thread Starter
       #3

    hi Superfly,

    will try that ASAP and post back here.

    thanks

    Jeff
      My Computer


  4. Posts : 399
    Windows 10 X64
    Thread Starter
       #4

    well Superfly,

    no difference, do not know what to do with it.

    result just the same, when renaming it to whatever, the space in the first position is gone then.

    but thanks


    jeff
      My Computer

  5. Superfly's Avatar
    Posts : 3,325
       #5

    OK PM me the Excel file if you would like me to have a look at how the code hangs together.

    It's a bit off topic here so I may reply via PM instead.
      My Computer


  6. Posts : 399
    Windows 10 X64
    Thread Starter
       #6

    ok, S will do that

    thanks

    Jef
    Last edited by jeffrys; 21 Oct 2014 at 04:35.
      My Computer

  7. Superfly's Avatar
    Posts : 3,325
       #7

    Jeff, I have replied to your post with what I think is the solution ... it's a common mistake made when concatenating strings to form a file path.

    Hope it works.
      My Computer


  8. Posts : 399
    Windows 10 X64
    Thread Starter
       #8

    ok S

    will check this

    thanks

    Jeff
      My Computer


  9. Posts : 399
    Windows 10 X64
    Thread Starter
       #9

    ok Superfly,

    you where right and it works. Thanks for your help, you saved my week....


    Jeff
      My Computer

  10. Superfly's Avatar
    Posts : 3,325
       #10

    You're welcome - happy to be of assistance :)
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 15:18.
Find Us




Windows 10 Forums