Posts by yoojrim

yoojrim · Apr 23rd 2012

Re: run-tim error 9: Subscription out of range error

I noticed that too so I tried without the space but it didn't make any difference.

yoojrim · Apr 23rd 2012

Re: run-tim error 9: Subscription out of range error

thanks but no luck. T.T

yoojrim · Apr 23rd 2012

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

Display More