Jmoz092
Board Regular
- Joined
- Sep 8, 2017
- Messages
- 184
- Office Version
- 365
- 2011
- Platform
- Windows
- MacOS
Hello,
I'm having problems getting a macro to run from a button in one of my workbooks.
The sheet names are dynamic, depending on which workbook the user is using within this project. Each Data sheet has a paired Invoice sheet. There are 20 or 30 of each, depending which type of workbook the user is using within this project.
On each "Data #########" worksheet, a person's name will appear in cell c4 if an event has occurred. Accordingly, costs incurred during the event will be calculated in cell D45 of the corresponding invoice sheet.
I'm trying to give the user a button to hide all Data and Invoice sheets in the workbook at the end of the month that have not been used. With the code as is, nothing happens when I run the macro, from the button or the macro window. Any help would be greatly appreciated. Thanks
I'm having problems getting a macro to run from a button in one of my workbooks.
Code:
Sub HideAllSheets()
Application.ScreenUpdating = False
'hide all sheets with no data
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Name Like "Data #########" And WorksheetFunction.CountA( _
Range("c4")) < 1 Then
ws.Visible = xlSheetHidden
End If
Next ws
For Each ws In ActiveWorkbook.Sheets
If ws.Name Like "Invoice #########" And Range("D45").Value = "$0.00" Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub
The sheet names are dynamic, depending on which workbook the user is using within this project. Each Data sheet has a paired Invoice sheet. There are 20 or 30 of each, depending which type of workbook the user is using within this project.
On each "Data #########" worksheet, a person's name will appear in cell c4 if an event has occurred. Accordingly, costs incurred during the event will be calculated in cell D45 of the corresponding invoice sheet.
I'm trying to give the user a button to hide all Data and Invoice sheets in the workbook at the end of the month that have not been used. With the code as is, nothing happens when I run the macro, from the button or the macro window. Any help would be greatly appreciated. Thanks