subscript out of range excel vba

Question

So i have a macro that does very basic things and it works like a charm. However, when anyone else tries opening the sheet and running the macro from their PC, they get the error "Subscript out of range" Please help, it is driving me crazy (:@)

Sub Refresh()

MSG1 = MsgBox("Are you Connected to (XY) Network?", vbYesNo, "?")
If MSG1 = vbYes Then

MsgBox "Refresh in Progress"
Workbooks("Contact Center Dashboard").Worksheets("CONTACT TRACKER").Activate
ActiveSheet.Range("A4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


'====================='
'    Unlock Sheets    '
'====================='

Workbooks("Contact Center Dashboard").Worksheets("SUMMARY").Unprotect Password:="nn"
Workbooks("Contact Center Dashboard").Worksheets("DETAILED").Unprotect Password:="nn"
Workbooks("Contact Center Dashboard").Worksheets("ANALYSIS").Unprotect Password:="nn"


'====================='
'      Initialize     '
'====================='

Dim ptc As PivotTable

'====================='
' Refreshing Dash - 1 '
'====================='

Set ptc = Workbooks("Contact Center Dashboard").Worksheets("SUMMARY").PivotTables("1. CALL SUMMARY - MAIN")
ptc.RefreshTable

'====================='
'      Lock Sheet     '
'====================='

Workbooks("Contact Center Dashboard").Worksheets("SUMMARY").Protect Password:="nn", AllowUsingPivotTables:=True
Workbooks("Contact Center Dashboard").Worksheets("DETAILED").Protect Password:="nn", AllowUsingPivotTables:=True
Workbooks("Contact Center Dashboard").Worksheets("ANALYSIS").Protect Password:="nn", AllowUsingPivotTables:=True


Workbooks("Contact Center Dashboard").Worksheets("SUMMARY").Activate

MsgBox "Dashboard Successfully Refresshed"

Else
MsgBox "You can still use the dashboard but the numbers will not be updated" & vbNewLine & vbNewLine & vbNewLine & "To get the latest update, do the following:" & vbNewLine & vbNewLine & "1- Please connect to XY local network or through VPN " & vbNewLine & "2- Click (REFRESH DATA)"

End If
End Sub

Which line does the error ocurr in? This error typically appears when you are trying to select something that doesn't exist. Possibly, the issue is that the workbook has a different name when opened on a different computer resulting in the `Workbooks("Contact Center Dashboard")` selector to fail. **Edit** seeing that you are protecting your sheets, are you disabling users from 'selecting locked / unlocked cells'? That could be another reason. Anyway, please let us know where the macro crashes. — nicolaus-hee, Apr 26 '15 at 12:12
I tried but it does not point anywhere. Just an error box and thats it — Nadz, Apr 26 '15 at 12:16
Is the "Debug" button grayed out? If yes, is your macro password protected? If yes, open the VBA Editor, enter the password, leave the window open and then run the macro again. Now the Debug button should be available. If all this fails, how are the other computers different from yours? I mean, how is their Excel environment different? Different file names, permissions, locked / unlocked / hidden sheets? — nicolaus-hee, Apr 26 '15 at 12:20
I changed the name of the sheet to "activeworkbook" and it worked. Thanks for the advice :) — Nadz, Apr 26 '15 at 12:23
I meant to say i Change the name on the sheet reference in the code — Nadz, Apr 26 '15 at 12:42

score 0 · Answer 1 · answered Apr 27 '15 at 04:07

This is the final working script for reference in case anyone has a simmilar issue in the future

Sub Refresh()

MSG1 = MsgBox("Are you Connected to (XY) Network?", vbYesNo, "?")
If MSG1 = vbYes Then

MsgBox "Refresh in Progress"
ActiveWorkbook.Worksheets("CONTACT TRACKER").Activate
ActiveSheet.Range("A4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

'====================='
'    Unlock Sheets    '
'====================='

ActiveWorkbook.Worksheets("SUMMARY").Unprotect Password:="nn"
ActiveWorkbook.Worksheets("DETAILED").Unprotect Password:="nn"
ActiveWorkbook.Worksheets("ANALYSIS").Unprotect Password:="nn"


'====================='
'      Initialize     '
'====================='

Dim ptc As PivotTable

'====================='
' Refreshing Dash - 1 '
'====================='

Set ptc = ActiveWorkbook.Worksheets("SUMMARY").PivotTables("1. CALL SUMMARY - MAIN")
ptc.RefreshTable

'====================='
'      Lock Sheet     '
'====================='

ActiveWorkbook.Worksheets("SUMMARY").Protect Password:="nn", AllowUsingPivotTables:=True
ActiveWorkbook.Worksheets("DETAILED").Protect Password:="nn", AllowUsingPivotTables:=True
ActiveWorkbook.Worksheets("ANALYSIS").Protect Password:="nn", AllowUsingPivotTables:=True


ActiveWorkbook.Worksheets("SUMMARY").Activate

MsgBox "Dashboard Successfully Refresshed"

Else
MsgBox "You can still use the dashboard but the numbers will not be updated" & vbNewLine & vbNewLine & vbNewLine & "To get the latest update, do the following:" & vbNewLine & vbNewLine & "1- Please connect to XY local network or through VPN " & vbNewLine & "2- Click (REFRESH DATA)"

End If
End Sub

subscript out of range excel vba

1 Answers1