Subscript Out Of Range (error 9)

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
94
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:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should add the extension to the filename with the Workbooks collection.
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
94
Office Version
  1. 365
Platform
  1. Windows
RoryA,

Thanks for your quick reply. Two things:
1) I am weak on VBA code, I modified this code from one I found via search. Can you let me know specifically where to add the extension?
2) If that solves the issue, why would it work on 3 of my computers but not the clients?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You'd add it wherever you use Workbooks("BD - Inversion Comunitaria EDR"). Is that the workbook with the code in it?
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The code is actually in the PERSONAL.XLSB workbook. does that matter?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, that's fine - although a strange place to store code that is shared between multiple users. If the code had been in that workbook, you could have just referred to ThisWorkbook rather than Workbooks("BD - Inversion Comunitaria EDR.xlsx") (or whatever the extension is).
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The code isn't being shared between multiple users, only for one user. Maybe we should move the code just to the BD - Inversion Comunitaria EDR workbook and try that?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is there a reason it isn't in that workbook? I can't see why you'd supply a client with your personal macro workbook. :)
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I copy the code on to their PERSONAL.XLSB workbook.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,729
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
OK, so is there a reason for it not to be in the workbook? Is it relevant for other workbooks (beyond the ones you're copying data from)?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

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
Top