Subscript Out Of Range (error 9)

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
245
Office Version
  1. 365
Platform
  1. Windows
I have a VBA code that pulls two worksheets from 4 different workbooks that are all in the same folder plus adds two blank worksheets at the end. The files that it pulls the worksheets from are all .xlsx files, while the workbook that they are pulled into is an .xlsm file. It works fine on three of my computers, but when I run it on the client's computer, I get a "Subscript Out Of Range (error 9)". I highlighted the area below that highlights when I debug the error. I do change the Path correctly. I am on Office 365 if that matters. Any thoughts on what could be causing this error?
Rich (BB code):
Sub ICEDR()

Dim Path As String
Path = "F:\Excel Help\Edna\Inversion Comunitaria EDR\"

Dim FileName As String
FileName = Dir(Path & "*.xlsx")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
    Workbooks.Open Path & FileName
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy After:=Workbooks("BD - Inversion Comunitaria EDR").Worksheets(Workbooks("BD - Inversion Comunitaria EDR").Worksheets.Count)
    Next ws
    Workbooks(FileName).Close
    FileName = Dir()
Loop

Worksheets(1).Delete

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Peticiones"
   
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "Proyectos"

End Sub
 
Last edited by a moderator:
Initially I thought we would need to run the macro on different workbooks, but as the process evolved, the plan to just run it on the "BD - Inversion Comunitaria EDR" workbook and then do a "Save As" and re-name it, means we are just re-using the same "BD - Inversion Comunitaria EDR" workbook and don't need it on the PERSONAL.XLSB workbook to be used elsewhere. So I moved it to the "BD - Inversion Comunitaria EDR" workbook. I also realized that there were hidden worksheets on the workbooks that were having sheets pulled into the "BD - Inversion Comunitaria EDR" workbook and on subsequent trial runs we didn't delete those hidden sheets and I think that also was causing a conflict. I also added the ".xlsm" to the end of the "BD - Inversion Comunitaria EDR" workbook in the code, but I really don't think that makes a difference. We will be retesting the macro soon and I am optimistic ?about it working.

Thanks for all your input.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
If you've moved the code to that workbook, you can simply use:

VBA Code:
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

and it will work no matter the name of the workbook.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top