Run-time error '9' ---- Code to fix included.
 

I have a problem with the following code and I cannot get it to run. I
am getting a "Run-time error
'9': Subscript out of range" error when I try to run this code. Here
is the code:


Sub openfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.Foun*dFiles(i))
'Perform the operation on the open workbook


----------- Errors on the following line:


wb.Worksheets("sheet1").Range(*"A1").Select


'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub


If anyone can solve this for me I would appreciate the help!


-Chris

Run-time error '9' ---- Code to fix included.
 

Had the same error a long time ago. Split the select argument. First select
the sheet, and then select the range.

HTH,

Rody

schreef in bericht
oups.com...
I have a problem with the following code and I cannot get it to run. I
am getting a "Run-time error
'9': Subscript out of range" error when I try to run this code. Here
is the code:


Sub openfilesInALocation()
Dim i As Integer, wb As Workbook
With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\chrisf\My Documents\vbatest"
..SearchSubFolders = False
..Filename = "*.xls"
..Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.Foun*dFiles(i))
'Perform the operation on the open workbook


----------- Errors on the following line:


wb.Worksheets("sheet1").Range(*"A1").Select


'Save and close the workbook
wb.Save
wb.Close
'On to the next workbook
Next i
End With
End Sub


If anyone can solve this for me I would appreciate the help!


-Chris

Run-time error '9' ---- Code to fix included.
 

Rody-

Thanks for the help.....but I am still getting the error. The problem
lies with how I am naming the sheet. The name of "Sheet1" is "Jun".
If I change the code to select "Jun" instead of "Sheet1" it runs fine.
How can I get it reference the first sheet in the file....not a
specifically named sheet?

Thanks for the help!
-Chris

Run-time error '9' ---- Code to fix included.
 

You can reference a worksheet by number instead of name. E.g,

Worksheet(1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
ups.com...
Rody-

Thanks for the help.....but I am still getting the error. The
problem
lies with how I am naming the sheet. The name of "Sheet1" is
"Jun".
If I change the code to select "Jun" instead of "Sheet1" it
runs fine.
How can I get it reference the first sheet in the file....not a
specifically named sheet?

Thanks for the help!
-Chris