Automatic saving and subsequent refresh

[WatchMC]

Dear community.

After hours of gruesome searching I still have not found a suitable solution to my problem. I have created a workboox with 3 sheets. The first sheet serves as a data entry sheet. Here I have a combination of checkboxes and text entry. Sheet number 2 is always visible and certain checkboxes will unhide sheet number 3. The rows for data entry on sheet 2 are hidden and will be unhidden by the checkboxes on sheet1. Essentially all the rows and columns in sheet2 are shown by the entries on sheet1. Im currently running a refresh macro (by button click) on sheeet one that will refresh the entire workbook. It works great except that sheet3 does not get automatically hidden again.

Private Sub CommandButton1_Click()
Dim i As Long
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
Dim WorkRange As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then Cell.Value = ""
Next Cell
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "CheckBox" Then
ActiveSheet.OLEObjects(i).Object.Value = False
End If
Next i
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Next
Worksheets("Startpage").Activate
End Sub

What I want to achieve:
I want to have a button on sheet2 that opens the saveas window and automatically creates a save file as follows "Project Overview_" & ActiveSheet.Range("B2").value & "_" $ "dateof today".xls
But I only want to save the sheets2 and 3 in that file and not the data entry sheet. If possible sheet3 should only be saved if it has been unhidden by the respective value entered on sheet1. Sheet2 should always be saved. And finally it would be awesome if there was a way to automatically have the refresh of the entire workbook happen at the same time (which means I could delete the button on sheet1).

Being a beginner with VBA trying to get this to work has been driving me crazy. Any expert help would be highly appreciated. Thank you.

Regrads,
Chris

 

[ClimoC]

add a line

Worksheets("Sheet3sName").Visible = xlhidden

Simplz

 

[WatchMC]

Dear ClimoC.

Thank you for your quick reply. Your solution works. Unfortunately this only helps with a very small part of my problem. Can anyone help me to solve the entire issue? That would be much appreciated. Thank you.

Best,
Chris

 

[ClimoC]

Dear ClimoC.

Thank you for your quick reply. Your solution works. Unfortunately this only helps with a very small part of my problem. Can anyone help me to solve the entire issue? That would be much appreciated. Thank you.

Best,
Chris

Couple of things you could try:

Dateoftoday thing, use

...Year(now()) & Month(now()) & Day(now())

gives 20130420

your not saving sheet3 is simple, but depends how you want to go about it. You can either use the macro to set Sheet3 to "Sheet3.Visible = xlveryhidden" - this makes it sooo hidden, tha right clicking the sheets and clikcing 'unhide' won't show it. You have to go into the VBE to unhide it. But the sheet still works for linkages etc

The other is to set a variable for a new workbook, call it WB2, and then do something like:

Wb1.Sheets("Sheet1").copy After:=Wb2.Sheets.count
Wb1.Sheets("Sheet2").copy After:=Wb2.Sheets.count

Wb2.Saveas Filename:="C:\Bobloblaw\Workbbok name_" & Year(now()) & Month(now()) & Day(now())  & ".xls"