VBA: Copy and Paste with Text Function

[ludlow]

Here is another one that is puzzling me. I am trying to copy data from worksheet1 to worksheet2. But the difficulty that I am incurring is the use of the Text function - Text(Cells,"#,##0.00").</SPAN>


I need to use the Text function to ensure that all numeric values are printed in Mail Merge to eliminate the dropping of trailing zeroes. Any assistance would be greatly appreciated. Below is a formula I tried to use with no avail.</SPAN>

WSD.Range("I2:I" & FinalRow).Copy Destination:=WSR.Range.Text(("I2" & LastRow), "#,##0.00")

 

[Joe4]

TEXT is a Property (and not a Function) in VBA.
If you are looking for the VBA equivalent to the Excel "TEXT" function, use "FORMAT".
See: MS Excel: FORMAT Function with Numbers (VBA)

 

[ludlow]

Thanks Joe4. I was able to get the data copied to the other spreadsheet with the format needed.

 WSR.Range("I2:I" & LastRow) = "=Text(DataDrop!I2,""#,##0.00"")"

 

[ludlow]

After more texting, it became apparent that the above code will not work as intended. However, with the suggestion of Joe4, I found something that will work

Sub hmm()
    Dim WSR As Worksheet
    Set WSR = Worksheets("CustRecords")
    FinalRow = WSR.Cells(Rows.Count, 1).End(xlUp).Row
    Set c = WSR.Range("J2:V" & FinalRow)
    c.NumberFormat = "#,##0.00;(#,##0.00)"
End Sub

For more info: Range.Text Property (Excel)