Active sheet?

[RJH]

I'm still very new to this stuff and I'm having a problem detecting the
active sheet. All I seem to accomplish is to activate sheet 2.
Obviously I'm using the wrong code here. I'm trying to detect which of 2
sheets (2 or 3) is active and print the correct header for that sheet. I'm
not even sure the else statement will work, I haven't got it to go that far!
Any light you can shed on this would be great.

Thanks!

RJH

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Worksheets("Sheet2").Activate Then
Dim wkSht As Worksheet
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht.PageSetup
.CenterFooter = "&12 " & _
Application.UserName & ", " _
& Format(Now(), "mmmm-dd-yyyy") & " &T"
.CenterHeader = "&20 &B" & "" _
& Format("Credit Card Reconciliation Statement" & Chr(10) _
& "Billing Date " & Worksheets("Sheet2").Range("M2"))
End With
Next wkSht

Else
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht.PageSetup
.CenterFooter = "&12 " & _
Application.UserName & ", " _
& Format(Now(), "mmmm-dd-yyyy") & " &T"
.CenterHeader = "&20 &B" & "" _
& Format("Credit Card Reconciliation Statement" & Chr(10) _
& "Billing Date " & Worksheets("Sheet2").Range("M2") & "Totals")
End With
Next wkSht
End If
End Sub

 

[Martin Seelhofer]

Hi there

Use the predefined object ActiveSheet to access the currently active
sheet. So, instead of [If Worksheets("Sheet2").Activate Then] use
something like [If ActiveSheet.Name = "Sheet2" Then].

However, I noticed that you have almost identical code in your
if- and else-branches. I suggest to use the following modification
of your code to prevent duplicates (not tested):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht.PageSetup
.CenterFooter = "&12 " & _
Application.UserName & ", " _
& Format(Now(), "mmmm-dd-yyyy") & " &T"
.CenterHeader = "&20 &B" & "" _
& Format("Credit Card Reconciliation Statement" & Chr(10) _
& "Billing Date " & Worksheets("Sheet2").Range("M2"))
' Here's the new code:
If ActiveSheet.Name = "Sheet2" Then
' append the string "Totals" to the center header
.CenterHeader = .CenterHeader & "Totals"
End If
End With
Next wkSht
End Sub


Cheers,
Martin

 

[Tim Zych]

For Each wkSht In ActiveWindow.SelectedSheets
Select Case wkSht.Name
Case "Sheet2"
With wkSht.PageSetup
.CenterFooter = "&12 " & _
Application.UserName & ", " _
& Format(Now(), "mmmm-dd-yyyy") & " &T"
.CenterHeader = "&20 &B" & "" _
& Format("Credit Card Reconciliation Statement" & Chr(10) _
& "Billing Date " & Worksheets("Sheet2").Range("M2"))
End With
Case "Sheet3"
With wkSht.PageSetup
.CenterFooter = "&12 " & _
Application.UserName & ", " _
& Format(Now(), "mmmm-dd-yyyy") & " &T"
.CenterHeader = "&20 &B" & "" _
& Format("Credit Card Reconciliation Statement" & Chr(10) _
& "Billing Date " & Worksheets("Sheet2").Range("M2") & "Totals")
End With
End Select
Next wkSht

 

[RJH]

Thanks for your help!

RJH