Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with setting Print area to more than one worksheet with VBA
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with setting Print area to more than one worksheet with VBA
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" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with setting Print area to more than one worksheet with VBA
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Area Setting | Excel Discussion (Misc queries) | |||
Setting The Print-Area ? | New Users to Excel | |||
Setting print area with vba | Excel Programming | |||
SETTING PRINT AREA IN VBA | Excel Programming | |||
Setting print area | Excel Programming |