Printing selected tabs from an array using visual basic

[Stevep4]

I am able to use a print code to print selected tabs from an Excel workbook:

Sub PrintTest()
Application.Dialogs(xlDialogPrinterSetup).Show
Worksheets(Array("Sheet1", "Sheet34")).PrintOut
End Sub
[code\]

What I would like to do is look at a list of tabs in a worksheet and print those tabs. For example, there might be a "Print_Page" tab with a list of 15 tabs that I would like to print. How can I say in the VB code to look at the sheet Print_Page and, say, cell A2, which would have the name of the tab I would like to print? Ideally, I would like to be able to print A2:A15.

Thanks

 

[Fluff]

How about

Sub printShts()
   Dim Ary As Variant
   
   With Sheets("Print_Page")
      Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   Sheets(Ary).PrintOut
End Sub

 

[Stevep4]

Thank you so much!

One question: Do you know how I can set it to print only one cell, or maybe 2 or 3? For example, I might want to set up code to print A2:A5 and then later run another job that prints A6:15.

Thanks

 

[Fluff]

Try

Sub printShts()
   Dim Ary As Variant
   Dim Sht As Worksheet
   With Sheets("Print_Page")
      Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   For Each Sht In Sheets(Ary)
      Sht.PageSetup.PrintArea = "$A$2:$A$5"
   Next Sht
   Sheets(Ary).PrintOut
End Sub

 

[Stevep4]

Thanks again! I decided to use the first code you gave me. One more thing. Is there any way to change the headers for the same array before or after printing. Here's what I have so far but am getting an error:

Sub changeheader()
   Dim Ary As Variant
   
   With Sheets("Print_Page")
      Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   Sheets(Ary).Sheets(Ary).PageSetup.RightHeader = NEW HEADER"
End Sub​

I'm getting an error when I try this at the pagesetup line.

Thanks

 

[Fluff]

I think you can only do that on 1 sheet at a time, so youl'd need to loop through the array