Need some help to modify this code to only run if a specific file name is open. The file is a canned report from SSRS, so I can't put the macro in the workbook, therefore the macro resides in my Personal.XLSB
I have tried a few variations of things found in the forums, but keep running into an error in the code one way (file isn't open) or the other (file is open).
So what I am trying to accomplish is....
If "PPM_Resource_Utilization_Detail_Report.xlsx" is open, then run the code to rename the tabs,
else display the message box to remind me that the file isn't open.
Currently the code below runs fine if the file is open, but gives an error (subscript 9) on the Set wb line and doesn't get down to the MsgBox line
Thx,
Don
I have tried a few variations of things found in the forums, but keep running into an error in the code one way (file isn't open) or the other (file is open).
So what I am trying to accomplish is....
If "PPM_Resource_Utilization_Detail_Report.xlsx" is open, then run the code to rename the tabs,
else display the message box to remind me that the file isn't open.
Currently the code below runs fine if the file is open, but gives an error (subscript 9) on the Set wb line and doesn't get down to the MsgBox line
VBA Code:
Option Explicit
Sub RenameSheetMumps()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim wb As Workbook
'If Not ActiveWorkbook Is Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx") Then
'MsgBox title:="ERROR", Prompt:="Can't run this unless " & ThisWorkbook.FullName & " is the active workbook."
'Exit Sub
'End If
Set wb = Workbooks("PPM_Resource_Utilization_Detail_Report.xlsx")
If wb.name = "PPM_Resource_Utilization_Detail_Report.xlsx" Then
For Each ws In Worksheets
If ws.name = "Sheet1" Then
ws.name = ws.Range("B7").Value
Else
ws.name = ws.Range("B6").Value
End If
Next
Else
MsgBox "Workbook not open", vbCancel
End If
Application.ScreenUpdating = True
End Sub
Thx,
Don