webb
05-18-2013, 04:31 PM
Hi there,
I am struggling with two print issues in an excel workbook. The workbook has sheets A, B, C and D.
Question 1
I always print to a PDF and have used the following macro which works well.
Sub PrintArrayOfWorksheets()
Dim vaWorksheets As Variant
vaWorksheets = Array("A", "B")
ThisWorkbook.Worksheets(vaWorksheets).PrintOut
Sheets("A").Select
The issue is that I now have additional sheets and the sheets to print can change but can be determined by the text in cell A1 on sheet A. So for example cell A1 might contain the text A, C, D. How do I re-write the macro second line to refer to the text in cell A1 in sheet A instead of the fixed values in the current macro?
Question 2
On Sheets B, C and D I have 8 named ranges (Report1, Report2...etc, all non-identical names). Before running the macro in question 1 how can I use vba to reset the print areas on each of these pages to the named ranges as defined in cell A1 on that sheet. For example, sheet B might have in cell A1, Report1, Report 3, Report 8 and these are the only ranges that should print from that sheet...
Thank you in advance, any help sincerely appreciated.
I am struggling with two print issues in an excel workbook. The workbook has sheets A, B, C and D.
Question 1
I always print to a PDF and have used the following macro which works well.
Sub PrintArrayOfWorksheets()
Dim vaWorksheets As Variant
vaWorksheets = Array("A", "B")
ThisWorkbook.Worksheets(vaWorksheets).PrintOut
Sheets("A").Select
The issue is that I now have additional sheets and the sheets to print can change but can be determined by the text in cell A1 on sheet A. So for example cell A1 might contain the text A, C, D. How do I re-write the macro second line to refer to the text in cell A1 in sheet A instead of the fixed values in the current macro?
Question 2
On Sheets B, C and D I have 8 named ranges (Report1, Report2...etc, all non-identical names). Before running the macro in question 1 how can I use vba to reset the print areas on each of these pages to the named ranges as defined in cell A1 on that sheet. For example, sheet B might have in cell A1, Report1, Report 3, Report 8 and these are the only ranges that should print from that sheet...
Thank you in advance, any help sincerely appreciated.