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

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

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).

--
Regards,
Tom Ogilvy



"KimberlyC" <[email protected]> wrote in message
news:[email protected]...
> 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.. 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




"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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).
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "KimberlyC" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

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



KimberlyC wrote:
>
> 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
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > 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).
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "KimberlyC" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >

--

Dave Peterson