VBA Tidying up help

Posted by Ian Mac on January 30, 2002 4:50 AM

All,

I have the following two Subs for each day of the week:

Private Sub CloseSunday_Click()
Application.ScreenUpdating = False
Sheets("TeamTotalsSun").Visible = False
Sheets("Michelle Nicoll Sun").Visible = False
Sheets("Robin Ellis Sun").Visible = False
Sheets("Caroline Swinhoe Sun").Visible = False
Sheets("Mark Farrow Sun").Visible = False
Sheets("Russell Haynes Sun").Visible = False
Sheets("Ian McAnena Sun").Visible = False
Sheets("Nick Renfern Sun").Visible = False
Sheets("Hamid Toghyan Sun").Visible = False
Sheets("Steven Morton Sun").Visible = False
Sheets("WeeklySummarySheet").Select
Application.ScreenUpdating = True
End Sub
Private Sub PrintSun_Click()

Sheets(Array("TeamTotalsSun", "Michelle Nicoll Sun", "Robin Ellis Sun", _
"Caroline Swinhoe Sun", "Mark Farrow Sun", "Russell Haynes Sun", _
"Ian McAnena Sun", "Nick Renfern Sun", "Hamid Toghyan Sun", _
"Steven Morton Sun")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

is there a way to have something similar (i.e. does the same thing) but stored in such a way that the user could select Monday from a list and run the code for sheets ending in Mon.

I'm trying to save on file size.

Many thanks

Ian Mac

Posted by Ivan F Moala on January 30, 2002 6:27 AM

Hi Ian
Following code assumes all days to printout ??
Have a look adjust as require in the select case
code......

Option Explicit

Sub Print_AnyDay()
Dim Sh As Worksheet
Dim x As Single
Dim sDay As String

Wrong:
sDay = Application.InputBox("Enter day to PrintOut", Type:=2)
If sDay = "False" Then Exit Sub

Select Case UCase(sDay)
Case "MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN"
GoTo StartSelection
Case Else
GoTo Wrong
End Select

StartSelection:
x = 1

Do
Sheets(x).Activate
If UCase(Right(ActiveSheet.Name, 3)) = UCase(sDay) Then Exit Do
x = x + 1
Loop

For Each Sh In ThisWorkbook.Sheets
If UCase(Right(Sh.Name, 3)) = UCase(sDay) Then
Sh.Select False
End If
Next
'Now Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

HTH

Ivan

Posted by Ian Mac on January 30, 2002 6:46 AM

hi, top stuff, but there's a but

I'm also attaching this to summary sheet and generally when the summary aheet is selected all other sheet are hidden, how can I Unhide the sheets first, I tried to add the line

Sheets(x).Visible = True

but it went babanas and unhid ALL the sheet in the book????

Thanks

Ian Mac