Johnsie007
New Member
- Joined
- May 11, 2020
- Messages
- 17
- Office Version
- 2010
- Platform
- Windows
Hi,
I am currently using a VBA macro to copy data from one worksheet into another workbook.
The VBA macro uses a loop as per the code below:
The macro works to begin with, however, it consistently displays the "Copy method of range class failed error" after approximately 400 loops.
The line which the macro fails is "Workbooks("(SmallOrds).xlsm").Sheets("Small Ords Portfolio").Range("A1:AA59").Copy Destination:=Workbooks("Returns.xlsx").Sheets("Test").Range(PasteRange)"
Can anyone please help me understand what is causing this error? Why does the macro work for 400 iterations before running into this error at the same point everytime?
Thanks,
Mike
I am currently using a VBA macro to copy data from one worksheet into another workbook.
The VBA macro uses a loop as per the code below:
VBA Code:
Sub LooperOffline()
Dim Count As Integer
Dim FirstColNum As Integer
Dim LastColNum As Integer
Dim PasteRange As String
Dim FirstColLet As String
Dim LastColLet As String
FirstColNum = 1
LastColNum = 27
Do Until Count = 1825
Application.Wait (Now + TimeValue("00:00:02"))
FirstColLet = WorksheetFunction.Substitute(Workbooks("Returns.xlsx").Sheets("Test").Cells(1, FirstColNum).Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=False), 1, "")
LastColLet = WorksheetFunction.Substitute(Workbooks("Returns.xlsx").Sheets("Test").Cells(1, LastColNum).Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=False), 1, "")
PasteRange = FirstColLet & 1 & ":" & LastColLet & 59
Workbooks("(SmallOrds).xlsm").Sheets("Small Ords Portfolio").Range("A1:AA59").Copy Destination:=Workbooks("Returns.xlsx").Sheets("Test").Range(PasteRange)
Workbooks("Returns.xlsx").Sheets("Test").Range(PasteRange).Value = Workbooks("(SmallOrds).xlsm").Sheets("Small Ords Portfolio").Range("A1:AA59").Value
Workbooks("Returns.xlsx").Sheets("Test").Columns.AutoFit
Workbooks("Returns.xlsx").Sheets("Test").Rows.AutoFit
Workbooks("(SmallOrds).xlsm").Sheets("Data").Range("AN1").Value = Workbooks("(SmallOrds).xlsm").Sheets("Data").Range("AN1").Value + 1
Workbooks("Returns.xlsx").Sheets("Test").Columns.AutoFit
FirstColNum = FirstColNum + 27
LastColNum = LastColNum + 27
Count = Count + 1
Loop
End Sub
The macro works to begin with, however, it consistently displays the "Copy method of range class failed error" after approximately 400 loops.
The line which the macro fails is "Workbooks("(SmallOrds).xlsm").Sheets("Small Ords Portfolio").Range("A1:AA59").Copy Destination:=Workbooks("Returns.xlsx").Sheets("Test").Range(PasteRange)"
Can anyone please help me understand what is causing this error? Why does the macro work for 400 iterations before running into this error at the same point everytime?
Thanks,
Mike