Select for printing

[Guest]

I have some code which produces a list of all the sheets in a Workbook, but I
want to add some user choice on which sheets are printed
ie on a sheet1 (a contents page basically)
COLA COLB
Sheet Name Print
sheet1 N
sheet2 Y
sheet3 N
sheet4 Y
ETC NB growing list of sheets to about 100
They will need to be grouped so page numbering works (1 of 50)

Anybody able to help?

 

[Zone]

This should work. Paste this code in a standard module. James

Sub PrintEm()
Dim k As Long, Ct As Integer
Dim Pg As Integer, BtmRow As Long
Dim ShtNm As String
With ThisWorkbook.Worksheets("Sheet1")
BtmRow = .Cells(.Rows.Count, "a").End(xlUp).Row
Ct = WorksheetFunction.CountIf(Range("b2:b" & BtmRow), "Y")
Pg = 1
For k = 2 To BtmRow
If .Cells(k, "b") = "Y" Then
ShtNm = .Cells(k, "a")
Worksheets(ShtNm).Activate
ActiveSheet.PageSetup.RightFooter = _
"Page " & Pg & " of " & Ct
ActiveSheet.PrintOut
Pg = Pg + 1
End If
Next k
End With
End Sub

 

[Guest]

hanks for quick reply...but couldn't make it work
No error message - just nothing happened

 

[Zone]

Saintsman, It works fine for me. Did you use copy and paste to put the code
in a STANDARD module and then run it? Try this. Before the line Pg=1, add
this line:
Msgbox BtmRow & " " & Ct
then you can see how many rows there are and how many Y's there are. Let me
know! James

 

[Guest]

OK that gives me an answer - but then nothing
Question - does this print out individual worksheets?

 

[Guest]

OK - FOUND IT - USED CAPITALS IN CODE !! lower case on sheet!
I should know better...Doh!

 

[Zone]

Saintsman, I've been away for a bit. Going okay now? James

 

[Guest]

Zone
the function works in that it selects the right sheets to be printed...however
Page numbering is wrong because the pages count actually counts worksheets,
so on first sheet it is 1 of 2 (single page), on second sheet 2 of 2 (3 pages
- ie should be x of 4)
Is there any way to group the selection as this may stop that problem?

Saintsman