An error message

[plainme]

What does the following error message means?

Run-time error '9'
Subscript out of range

Thanks!

 

[diddi]

can you post the code.
there are a number of different possibilities for this error from a misspelt sheet name to wrong number of variables in an array..

 

[plainme]

Sure. I put a list of file names with path in "File Path". I intended to use the following code to open the workbook from "File Path" and copy some range from tabA and tabB to paste on tab 2011Data. Then, go to the next workbook from "File Path" to repeat the copying and pasting.

Many Thanks!


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim i As Integer
Dim WorkbookName As String
Dim RemoteWkbk As Excel.Workbook, CurrWkbk As Excel.Workbook

i = 1
While Len(Range("File Path").Offset(i, 0)) > 0

' Open workbook
WorkbookName = Workbooks("File Path").Offset(i, 0)
'Open workbook
Set RemoteWkbk = Workbooks.Open(WorkbookName)


' Copy contents
x = RemoteWkbk.Worksheets("tabA").Range("A9:B12").Value
y = RemoteWkbk.Worksheets("tabB)").Range("A8:D20").Value
' Paste contents
CurrWkbk.Worksheets("2011Data").Range("P1").Offset(13 * (i - 1), 0).Resize(UBound(x, 1), UBound(x, 2)) = x
CurrWkbk.Worksheets("2011Data").Range("R1").Offset(13 * (i - 1), 0).Resize(UBound(y, 1), UBound(y, 2)) = y

RemoteWkbk.Close False

i = i + 1
Wend

Application.ScreenUpdating = False
End Sub

 

[djreiswig]

y = RemoteWkbk.Worksheets("tabB)").Range("A8:D20").Value

Could this extra parenthesis be your problem?

 

[diddi]

thats about it, i'd think

 

[plainme]

I fixed it but still have an error message:

Run-time error '1004':
Application-defined or object-defined error

Thanks!

 

[diddi]

what line does it stop at (use debug)

 

[djreiswig]

Not the cause of the error, but you have screenupdating = false at the beginning and end of your sub. Should one of those be true?

 

[plainme]

Stopped at the first line:

Private Sub CommandButton1_Click()

 

[djreiswig]

Where is this code located? Module, UserForm, Worksheet?