Unhide sheet macro

Resolved · High Priority · Version Standard

Tue 1 Apr 2008, 10:57 Delegate Nick said...

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

Mon 7 Apr 2008, 06:10 Trainer Mara said...

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

Mon 7 Apr 2008, 11:34 Delegate Nick said...

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

Mon 21 Apr 2008, 01:27 Trainer Mara said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Excel 2010 Shortcuts - Start and End of the Worksheet

Did you know you can quickly move to either the beginning or the end of an Excel Worksheet using just a couple of keys on the keyboard?

Press Ctrl + Home key to move the cursor to cell A1

Press Ctrl + End key to move the cursor to the end of the current worksheet.

View all Excel hints and tips