VBA to Hide Sheet1 & 2 after a 'save as' has been performed

[Jwilf]

Hi,
I have seen this happen before on a workbook I have used.

I am after hiding 2 sheets once the 'Save as' has been performed. on the workbook I used with this on, you had to 'save as', close and then re open the new saved as file and the sheets in question have been hidden (the where open to begin with, you entered the source data and then saved as).

Does any one have any ideas?

Thank you

[alansidman]

I am confused by your explanation. Do you wish to hide sheets after saving the workbook, but keep the workbook open. In this case, the sheets are not hidden when the workbook is opened. Or do you wish for the sheets to be hidden when the workbook is opened? Please clarify in the simplest terms.

[Jwilf]

I am confused by your explanation. Do you wish to hide sheets after saving the workbook, but keep the workbook open. In this case, the sheets are not hidden when the workbook is opened. Or do you wish for the sheets to be hidden when the workbook is opened? Please clarify in the simplest terms.

Hi sorry, reading back through my post i realised i didnt make it clear enough.

I have created a report template for some one else to use. Sheet 1 and 2 are the data in put sheets, the rest of the sheets contain all of the pivot tables and charts.

Because some one else will be using the template i would like a macro that automaticaly hides sheet 1&2 in the saved as document. So all sheets in the original document remain open (as this document is the template to be used over and over again) but when they save a copy, and then open that copy the sheets are hidden (the saved copy is the report to present to others).

I hope that makes more sense.

[alansidman]

Assume data sheets to be hidden are in Sheet1 and Sheet2.

Open the VBE and place this code in the Workbook_BeforeSave Event

Please Login or Register  to view this content.

To unhide these sheets,
Insert this VBA in the Modules

Please Login or Register  to view this content.

For the second code:


How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Jwilf]

Perfect,
thank you for your help

[Jwilf]

sorry to pester.
I have the code in the workbook, I don't want the code to work when I save the original workbook as this is the template to be used time and time again and the first 2 sheets need to always be open in order for the other user to input the data. I have a save as code in the work book linked to a button (see below). the save as copy is the report this user will then send on to other people to view, hence why I want the data input sheets to be hidden in this version.
The user only has data input skills and does not know how to add vba or even right click the sheet select hide so I am trying to automate as much as I can.

Thanks in advance

Please Login or Register  to view this content.

[alansidman]

Maybe

Please Login or Register  to view this content.

Then unhide the sheets before closing the original file or unhide them on Worksheet_Open event