Nick has attended:
Access Introduction course
Unhide sheet macro
I have recorded a macro so that I can hide and unhide sheets within a particulr workbook. However, whenever I delete a sheet, add a sheet or change the name of a sheet the macro fails and requires updating.
Is it possible to record a macro that will hide or unhide sheets even when new sheets are added, deleted or their names are changed?
Cheers
NICK
RE: Unhide sheet macro
Hello Nick,
Thank you for your post. I have tried to replicate this issue, but I do not get the same problem. I would be interested in seeing the VBA for this macro. Could you please press ALT+F11, find the folder on the left side called "Modules" and find the module that has the particular macro? If you could send me the code that appears, it may help me better understand why this is failing for you.
Have a great day.
Regards,
Mara
Microsoft Office Specialist Master Trainer
RE: Unhide sheet macro
Mara
To recap, looking to write a macro to unhide any hidden sheets in a workbook.
See the macro below (I have first hidden all sheets then unhidden all sheets) - again, the macro is fine until I decide to change a name, add or delete a sheet - when I do this I have to manually update the macro for any changes made to sheets or sheet names.
Cheers
NICK
Sub Unhide()
'
' Unhide Macro
' Macro recorded 07/04/2008 by nickm
'
' Keyboard Shortcut: Ctrl+u
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
"Sheet8", "Sheet9", "Sheet10")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True
Sheets("Sheet9").Visible = True
Sheets("Sheet10").Visible = True
Sheets("NOTES").Select
Range("B2").Select
End Sub
RE: Unhide sheet macro
Hi Nick,
You will indeed have some issues if you rename your sheets, because the macro is looking for sheets with names like "Sheet1". If you edit the VBA and change the sheet names there, it should work.
Please let me know if this did the trick for you.
Regards,
Mara
