Dynanic Print Range
Having a small problem with setting up a dynamic print range. I have defined
a range by clicking insertnamedefine. Named range is PrintArea and below is the formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4) I have also inserted the following vba code into the workbook: Private Sub Workbook_BeforePrint (Cancel As Boolean) With ActiveSheet ..PageSetup.PrintArea = .Range(.Range("A1"), ..Range("PrintArea").Cells(.Range("PrintArea").Cel ls.Count)).Address This setup does exactly what I need with a workbook with only 1 worksheet. However, I need help on adjusting this to work with a workbook with multiple worksheets. Can the dynamic range be defined in vba using ActiveSheet, and if so, what would that look like? I'm looking for something other than a dynamic print range for every worksheet. |
Dynanic Print Range
So long as you are only using the english version of XL you do not need the
code. XL stores the print area as a locally defined named range called Print_Area. If your dynamic named range was changed to include the under score in the name and declared local to the sheet then you would get a dynamic print area. IYou would still need to create a dynamic named range formula for each sheet byt that is generally not too bad to do. You could even do it via code if there were enough sheets to warrant the effort. -- HTH... Jim Thomlinson "hurlbut777" wrote: Having a small problem with setting up a dynamic print range. I have defined a range by clicking insertnamedefine. Named range is PrintArea and below is the formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4) I have also inserted the following vba code into the workbook: Private Sub Workbook_BeforePrint (Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A1"), .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)).Address This setup does exactly what I need with a workbook with only 1 worksheet. However, I need help on adjusting this to work with a workbook with multiple worksheets. Can the dynamic range be defined in vba using ActiveSheet, and if so, what would that look like? I'm looking for something other than a dynamic print range for every worksheet. |
Dynanic Print Range
Have you defined Name (PrintArea) include the Sht Name like code below. You
don't need the period in front of the named Range unless you have more than one workbook opened. either Range("PrintArea") or workbooks("Book1.xls").Sheets("Sheet1").Range("Pri ntArea") A Named Range is a weird item. It is a worksheet object but is really a workbook object. If you look at the named ranges on the worksheet menu Insert - Names you see the Sheetname but the name only returns the range without the sheet. You can only get the sheet name by using RefersTo. Sheet1PArea Sheet2PArea Sheet3PArea The in your code use the sheet name to get the area for each sheet for each sht in sheets PrintArea = Sht.Name & "PArea" .PageSetup.PrintArea = Range(.Range("A1"), _ Range(PrintRange).Cells(Range(PriontArea).Cells.Co unt)).Address next Sht "hurlbut777" wrote: Having a small problem with setting up a dynamic print range. I have defined a range by clicking insertnamedefine. Named range is PrintArea and below is the formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4) I have also inserted the following vba code into the workbook: Private Sub Workbook_BeforePrint (Cancel As Boolean) With ActiveSheet .PageSetup.PrintArea = .Range(.Range("A1"), .Range("PrintArea").Cells(.Range("PrintArea").Cell s.Count)).Address This setup does exactly what I need with a workbook with only 1 worksheet. However, I need help on adjusting this to work with a workbook with multiple worksheets. Can the dynamic range be defined in vba using ActiveSheet, and if so, what would that look like? I'm looking for something other than a dynamic print range for every worksheet. |
All times are GMT +1. The time now is 10:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com