I am trying to copy a Range from one worksheet to another worksheet. The source workbook and worksheet are in different workbooks, with the macro running in the target workbook. I open the source workbook in a separate Excel application object that is not visible. The range is about 1,000 lines with 15 columns. In the source data there are some cells with numbers stored as text and I need to preserve them as text.
When I just assign Value or Value2, unfortunately the text cells with number in them get converted to numbers. This is the code I tried:
targetRange.Value = sourceRange.Value
or
targetRange.Value2 = sourceRange.Value2
Using Copy does not work, first I get message box saying that Excel is waiting for another OLE application, and then after a long time says the Copy command has failed. This is the code I used:
sourceRange.Copy targetRange
What copy method could work in this scenario? From two workbooks in two Excel applications, preserving numbers stored as text as such.
This is an example of resolving the issue.
Sub anotherwb()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Set app = CreateObject("Excel.Application")
Set wb2 = app.Workbooks.Open("C:\users\username\desktop\wb1.xlsm")
wb2.ActiveSheet.Range("A1:A10").Copy
wb1.ActiveSheet.Range("A100").PasteSpecial xlPasteValues
wb2.Close
Set app = Nothing
End Sub
Take care that empty cells at the end of the range are not included in the copied range. If you define in wb1.ActiveSheet.Range("A100").PasteSpecial xlPasteValues the complete range like A100:A109 and the last cell is empty in the source range a warning message will be pop up.