I receive an excel spreadsheet of daily orders received which I copy to a new workbook (with a macro) and then
run another macro that does some formatting and other things ready for the data to be used in a MS Word mail merge for
customer welcome letters and order receipts (don't ask why, I just do it!).
Here's the problem. The original sheet
has columns E to H formatted as USD currency ([$$-409]#,##0.00) and even though I use the following code to paste the sheet
information to the destination new workbook:
Windows("new_orders.xlsm").Activate 'this is the original workbook
Lastrow = Sheets("orders").Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:XFD" & Lastrow).Select
Windows("new_customers.xlsm").Activate 'this is the destination workbook
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Selection.NumberFormat = "@"
I still end up with the destination formatting for columns E to H being of type currency (£#,##0.00) although it is
now in GBP for some unknown reason..... but I need it as text so that it imports correctly to word mail merge.
can I change the cell format in VBA so that it is text?
There is a second problem. When the value of the cell is
an exact pound amount, e.g. £32.00, if I manually convert it to format type text, it changes the cell contents to 32 when I
actually want it to be 32.00.
Any ideas on how I can take a currency format and change it to text without losing
the post decimal place digits when they are both zeros?
Any help would be greatly appreciated!
P.S. I'm using Office 2007