98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Unhide sheet macro
Unhide sheet macro
Resolved · High Priority · Version Standard
Tue 1 Apr 2008, 10:57Delegate 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:10Trainer 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:34Delegate 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:27Trainer 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 information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Excel 2010 Shortcuts - Start and End of the WorksheetDid 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? |
