Help with setting Print area to more than one worksheet with VBA

[KimberlyC]

Hi

I am running this code below (with help from this newsgroup) to set the
print area of the Active Worksheet to the last entry in col. b.
It works great...
However.. I now need to set the print area based on the same info (col b
"last entry" and A1:Q) for each worksheet that "starts" with the name
"Payroll..."
in the ActiveWorkbook.
I'm not sure how to do this..or if it can be done..
Any help would be greatly appreicated!!

Private Sub CommandButton1_Click()

ThisWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:Q" &
LastInColumn(Range("b1"))

''get rid of this after testing
MsgBox "Print area set to " &
ThisWorkbook.ActiveSheet.PageSetup.PrintArea
End Sub

Function LastInColumn(rngInput As Range)
''Courtesy of http://www.j-walk.com, though a tad changed
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LastInColumn = WorkRange(i).Row
Exit Function
End If
Next i
End Function


Thanks in advance!
Kimberly

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
set sh1 = Activesheet
For each sh in thisworkbook.Worksheets
if Left(lcase(sh.name),7) = "payroll" then
sh.Activate
ThisWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:Q" & _
LastInColumn(sh.Range("b1"))
end if
Next
sh1.Activate
End Sub

Assume each printarea should be unique to that sheet in terms of the
lastincolumn determination (rather than set to match the determination for
the activesheet).

 

[KimberlyC]

Tom.. Thanks so much for the code..
I've tried this and it works fine with the sheets that have Payroll in the
beginning of their names.
I have decided to change the worksheets names that start with "Payroll" to
start with "Haz"
I change the code as shown below...but it does not do anything.
Any help would be greatly appreicated...

Private Sub CommandButton1_Click()

Set sh1 = ActiveSheet
For Each sh In ThisWorkbook.Worksheets
If Left(LCase(sh.Name), 3) = "Haz" Then
sh.Activate
ThisWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:Q" & _
LastInColumn(sh.Range("b1"))
End If
Next
sh1.Activate
End Sub

Function LastInColumn(rngInput As Range)
''Courtesy of http://www.j-walk.com, though a tad changed
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LastInColumn = WorkRange(i).Row
Exit Function
End If
Next i
End Function

 

[Dave Peterson]

This line:
If Left(LCase(sh.Name), 3) = "Haz" Then
is looking for lower case letters.

Try:
If Left(LCase(sh.Name), 3) = "haz" Then