Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area to text
Hi all
I want to set the length of my print area by the text "Overall Total" in column B. It's location can vary by up to 500 rows. My print area always starts at cell A1:H?. ? being the row that contains "Overall Total" At the moment I have the print area set to A1:H200. Can anyone tell me how to write the macro code to set the print area to the row containing "Overall Total". My sheet name is "Printout". & I'm using Excel 2003 Thanks in advance Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area to text
Hi Brian,
Try: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim LRow As Long Const sStr As String = "Overall Total" Set WB = Workbooks("Your Book.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH LRow = .Cells.Find(What:=sStr, _ After:=.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Row End With SH.PageSetup.PrintArea = "$A$1:$H$" & LRow End Sub '<<============= --- Regards, Norman "BrianW" wrote in message ... Hi all I want to set the length of my print area by the text "Overall Total" in column B. It's location can vary by up to 500 rows. My print area always starts at cell A1:H?. ? being the row that contains "Overall Total" At the moment I have the print area set to A1:H200. Can anyone tell me how to write the macro code to set the print area to the row containing "Overall Total". My sheet name is "Printout". & I'm using Excel 2003 Thanks in advance Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting print area to text
Norm thanks for that your help.
Code works fine. Had to twik the code slightly as I should have mentioned that the name of the work book changes each time I use it. Here's my final code in case other newbees may be interested Public Sub Macro1() Let FName = Worksheets("Pricing").Cells(5, 12) '<<== Reference to new file name Dim WB As Workbook Dim SH As Worksheet Dim LRow As Long Const sStr As String = "Overall Total" Set WB = Workbooks(FName) '<<==== CHANGE Set SH = WB.Sheets("Printout") '<<==== CHANGE With SH LRow = .Cells.Find(What:=sStr, _ After:=.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Row End With SH.PageSetup.PrintArea = "$A$1:$G$" & LRow End Sub Once again thankyou for your time and your assistance. It's greatly appreciated. Have a great day |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Area Setting | Excel Discussion (Misc queries) | |||
Setting The Print Area | Excel Programming | |||
Setting print area with vba | Excel Programming | |||
setting print area via VBA | Excel Programming | |||
Setting Print Area, includes text, numbers, charts & graphs | Excel Programming |