Run-Time Error '9'

[Bomba]

Hi,
I have a folder named "My System" with 31 identical workbooks (except the name). Since I use these workbooks on my PC, everything work good but I started to have a problem when I copied the folder from my PC to my PC at work.
Lets work on workbook named "2".
I have a command button to run this code:

Code: Select all

Sub SO()
    Dim strPath As String
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")

dateStamp = Date

If Not Range("Q1").Value = dateStamp Then
    Range("Q1").Value = dateStamp
'// replace with your code below-------------
Workbooks("2").Sheets("Chit1&2").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit1&2'!E20:H21"
Workbooks("2").Sheets("Chit3&4").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit3&4'!E20:H21"
Workbooks("2").Sheets("Chit5&6").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit5&6'!E20:H21"
Workbooks("2").Sheets("Chit1&2").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit1&2'!J8"
Workbooks("2").Sheets("Chit3&4").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit3&4'!J8"
Workbooks("2").Sheets("Chit5&6").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit5&6'!J8"
    MsgBox "Code run for first time today!"
Else
    MsgBox "Code has already been run today!"
End If

End Sub

When I click the button, it appears this message:

Run-Time Error '9'

Subscript out of range

After I debug, the line ( Workbooks("2").Sheets("Chit1&2").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit1&2'!E20:H21") becomes highlighted in yellow. From my very little knowledge in VBA, I am suspected that the problem is (strPath). What you think?

Thanks in advance

[HansV]

Do you have a folder named My System on the desktop of your PC at work, with a subfolder named 1. January 2019?
And if so, does the subfolder contain a workbook 1.xlsm?

[Bomba]

Yes

[HansV]

Is the workbook named "2" open in Excel when you run the macro?
If it is, does it help if you change Workbooks("2") to Workbooks("2.xlsm") ?

[Bomba]

Yes, it is open.
On my PC workbook is showed as ("2") and it works fine, but when I copied to my work's PC, it appears as ("2.xlsm").
Just to let you know that on my PC I am using (Excel 2010) and on my work's PC I am using (Office 365).
Thanks

[HansV]

So, does it work if you use 2.xlsm?

[Bomba]

No.

[HansV]

We know that:
- The path is correct.
- The file names are correct.
The remaining possible causes are:
- The workbook 1.xlsm doesn't contain a sheet named Chit1&2 (spelled exactly like that).
- The workbook 2.xlsm doesn't contain a sheet named Chit1&2 (spelled exactly like that).

[Bomba]

Hi,
I don't know what is happening, I just copied the folder from my PC to my work's PC.
On my PC it works and on my work's PC doesn't.
Master, I will answer for your replies tomorrow since I have to go.

Thanks a lot

[Bomba]

Hi,
Will I send you the folder so that it will be more easily to trace the problem?

Thanks

[HansV]

You can do that. Do you still have my email address?

[Bomba]

I was going to give you a Dropbox link.

[HansV]

That's fine!

[Bomba]

Hi,
The Command button is in sheet named Dash.
https://www.dropbox.com/sh/bo1mep0zrukw ... -3PXa?dl=0

Thanks

[HansV]

If I change "2" to "2.xlsm", the macro runs successfully on my PC.

Code: Select all

Sub SO()
    Dim strPath As String
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    dateStamp = Date
    If Not Range("Q1").Value = dateStamp Then
        Range("Q1").Value = dateStamp
        Workbooks("2.xlsm").Sheets("Chit1&2").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit1&2'!E20:H21"
        Workbooks("2.xlsm").Sheets("Chit3&4").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit3&4'!E20:H21"
        Workbooks("2.xlsm").Sheets("Chit5&6").Range("E20:H21").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit5&6'!E20:H21"
        Workbooks("2.xlsm").Sheets("Chit1&2").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit1&2'!J8"
        Workbooks("2.xlsm").Sheets("Chit3&4").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit3&4'!J8"
        Workbooks("2.xlsm").Sheets("Chit5&6").Cells(8, "J").Value = "=" & "'" & strPath & "\My System\1. January 2019\[1.xlsm]Chit5&6'!J8"
        MsgBox "Code run for first time today!"
    Else
        MsgBox "Code has already been run today!"
    End If
End Sub

S3485.png

[Bomba]

Hi Master,
It worked perfectly.
Is there a way that I will change "2" to "2.xlsm" in all 31 workbooks at one go?

Thanks in advance

[HansV]

Not easily, but you can replace all occurrences in one workbook in one go, so it shouldn't take too long to do it manually.

[Bomba]

Thanks a lot Master for your great help.