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
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...
hi Superfly,
will try that ASAP and post back here.
thanks
Jeff
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
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.
ok, S will do that
thanks
Jef
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.
ok S
will check this
thanks
Jeff
ok Superfly,
you where right and it works. Thanks for your help, you saved my week....
Jeff
You're welcome - happy to be of assistance
