Solved: VBA to to unhide sheet

[koala]

Can someone please assist.

I have a workbook with 8 sheets. (Main, Monday,Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)

All sheets except Main are hidden. In cell B2 of Main is a date. I want to open a sheet that corresponds to the day of the date in cell B2, and hide all the rest, and then paste the date into G1 of the visible worksheet.

To clarify, if Main B2 was Saturday 4 September 2010, Main would get hidden and Saturday would be made visible and the date from B2 would then be pasted into cell G1 of the visible (Saturday) worksheet.



Thanks in advance
Koala

[Ken Puls]

Sub TransferDate()
With Worksheets(Format(Worksheets("Main").Range("B2").Value, "dddd"))
.Visible = xlSheetVisible
.Range("G1") = Worksheets("Main").Range("B2").Value
.Select
End With

Worksheets("Main").Visible = xlSheetHidden
End Sub

[Simon Lloyd]

You might want to use this in your thisworkbook module so that you only display the Main sheet on open.

Private Sub Workbook_Open()
Dim Sh As Worksheet
Sheets("Main").Visible = True
For Each Sh In Sheets
If Sh.Name <> "Main" Then
Sh.Visible = xlVeryHidden
End If
Next Sh
End Sub

[Ken Puls]

LOL! I probably shouldn't have, but I took it for granted that he'd already got that far. Thanks for picking up after me here, Simon. ;)

[Simon Lloyd]

LOL! I probably shouldn't have, but I took it for granted that he'd already got that far. Thanks for picking up after me here, Simon. ;)To be fair the OP doesn't state whether they want to be able to go back to the Main sheet at anytime or whether only that days worksheet should be visible :)

[koala]

Thanks Ken and Simon,

I already had the open workbook event and each worksheet has a button to return to main page hiding the others.

Ken, your code does exactly as I asked, however it has created another problem for me. Previously I had a validation list in G1 on the day sheets to change dates manually, and this then triggerred a Worksheet Change event on that sheet that calls other subs to do an number of things including colour certain cells when conditions are met. For some reason, when using your code this has stopped on the day sheet, but occurred on the Main sheet (which doesnt have a Worksheet Change routine).

It would appear that once the day sheet becomes visible, the Worksheet Change event triggers, however that Main sheet must still be the active sheet and this line hasnt yet executed.

.Range("G1") = Worksheets("Main").Range("B2").Value

(I dont understand why the routine is firing on the Main Sheet even though the code is on the day sheet)

I thought of placing the Worksheet Change routine at the end of your routine, however each day sheet has a different routine so this wouldnt work.

Any thoughts?

Koala

[Simon Lloyd]

Ken's code cannot and does not change anything in your workbook except the activesheets G2, if you make a sheet visible it automatically becomes the active sheet, from what i understand G2 is changing and there for triggering the event.

Can you supply a sample workbook thats displaying this behaviour?

[koala]

Thanks Simon,

The day sheet, even though visible is not active.

When the code runs, the day sheet does not become active until the Main sheet is hidden.

To overcome this I added

.Activate

before

.Range("G1") = Worksheets("Main").Range("B2").Value

and the Worksheet Change event now works fine.

Thanks for your help

cheers
Koala