Macro printing to refer to cell and named ranges

[webb]

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.

[SamT]

Q1:vaWorksheets = Split(Sheets("A").Range("A1").Text , ",")

[p45cal]

cross post:
http://www.mrexcel.com/forum/excel-questions/703601-macro-printing-refer-cell-named-ranges.html

[SamT]

Q2: First have to put the range names in an array X X = Split(Sheets("B").Range("A1").Text , ",")
Then get and concatenate the [A1] addresses into a String Y Addendum = ", "
For i = 0 To UBound(X)
'No trailing ", "on last item
If i = UBound(X) Then Addendum = ""
Set Y = Y And Range(X(i)).Address & Addendum
Next i
Now set the PrintArea for the desired SheetSht.PageSetup.PrintArea = Y
Anyway, I think that'll work :dunno

[webb]

Hi Sam,

Thank you very much for your replies. My VBA is not very good but I am trying to learn and improve each time. Two questions please;

Regarding your code for question 1, how should I show the text in cell A1 where multiple sheets are involved? I.e. if I have just A in cell A1 the code works, but if I put A, B in cell A1 I get an error?

Regarding your code for question 2, how would this work in total in terms of the code I would use (do I combine the text you have in the three steps)? Also, for the print area it is always on sheets B, C and D landscape format with 2 pages per 1 printed page, as many printed pages as possible to fit the required number of reports. is there a way to show this in the code?

I apologise for asking what is probable very simple, thank you for your assistance.

[SamT]

Because you want to learn.
Sub test()
Dim X 'This is the only time to declare a variable without also setting it's Type
X = What you want to see the value of
End Sub The Two lines in Sub Test are very handy for learning and troubleshooting code. Use F8 to step thru sub Test and watch the value of X by placing the Mouse Cursor over "X." Other people like to use Debug.Print X. Learn to use both and see which works better in different situations. Sub Test itself, is handy for testing User Defined Functions

The easiest way to find help on a subject is to type the Keyword into a VBA code page, leave the cursor next to, or in, the Key Word and press F1.

Memorize the next line.
The most common Keywords you will look up are Range, which is a member of Worksheets, which is a member of Workbooks, which is a member of Application.Excel.

For more advanced study, other members of Application.Excel are Charts, PivotTables and UserForms. VBA itself, handles all MS Office Applications.

Q1. Combining Answers 1 & 2
vaWorksheets = Split(Sheets("A").Range("A1").Text , ",") 'String
For i = 0 To UBound(vaWorksheets) 'Iterate (loop) thru vaWorksheets

Addendum = ", "

Sht = Sheets(vaWorksheets(i)
X = Split(Sheets("B").Range("A1").Text , ",")
For j = 0 To UBound(X)

'No trailing ", "on last item
If j = UBound(X) Then Addendum = ""
Set Y = Y & Range(X(i)).Address & Addendum
Next j

'Q2. Print Setup
With Sht.PageSetup
.PrintArea = Y
.Orientation = 2 'xlLandscape
.FitToPagesWide = 2
.FittoPagesTall = 1
End With

Next i Note that I am leaving it up to you to Declare all the Variables. I suggest that you find meaningful name for all except "i" and "j", which are universally understood to be Loop counters.

The very first thing to Declare is at the very top of the code page: Option Explicit.

Hint. all the keywords above are emboldened.