Hello, Can someone please help?
I am a little frustrated because this code did work just fine before I copied the code to another spreadsheet. The new spreadsheet still has the same number of tabs and tab names so I am not sure why it is not working.
I am trying to copy contents from multiple spreadsheets into one main spreadsheet. I CAN copy from the first tab of multiple spreadsheets into the main one. That works fine (sub Overdraft_MECH). But when I run sub Overdraft_NCCS, that's when I get the out of range error message. Can someone please help?
Out of error essage happens in sub O_2.
Thanks.
Code
sub O_1()
Dim myDir As String 'directory path name where workbooks are saved
Dim amWB As String 'data source (individual workbooks completed by AMs)
Dim st_row As Integer 'row number - where data copy starts
Dim index_num As Integer 'numbers in column A to label testings copied from individual workbook
Dim x As Integer
Dim row_A As Integer 'starting row for index_num
Application.ScreenUpdating = False
myDir = "G:\a\"
amWB = Dir(myDir & "*.xls")
Do While amWB <> ""
If amWB <> ThisWorkbook.Name Then
With Workbooks.Open(myDir & amWB)
st_row = 21
Do
With .Sheets("1) Overdraft - MECH ").Range("B" & st_row, Range("M" & st_row)).Copy
ThisWorkbook.Sheets("1) Overdraft - MECH ").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End With
st_row = st_row + 1
Loop Until IsEmpty(Range("B" & st_row))
.Close False
End With
End If
amWB = Dir
Loop
Application.ScreenUpdating = True
End Sub
Sub O_2()
Dim myDir As String
Dim amWB As String
Dim st_row As Integer
Dim index_num As Integer
Dim x As Integer
Dim row_A As Integer
Application.ScreenUpdating = False
myDir = "G:\a\"
amWB = Dir(myDir & "*.xls")
Do While amWB <> ""
If amWB <> ThisWorkbook.Name Then
With Workbooks.Open(myDir & amWB)
st_row = 22
Do
With .Sheets("2) Overdraft - NCCS (R925) ").Range("B" & st_row, Range("M" & st_row)).Copy <--- out of range error here
ThisWorkbook.Sheets("2) Overdraft - NCCS (R925) ").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End With
st_row = st_row + 1
Loop Until IsEmpty(Range("B" & st_row))
.Close False
End With
End If
amWB = Dir
Loop
Application.ScreenUpdating = True
End Sub