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?

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

"Saintsman" wrote in message
...
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?

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



"Zone" wrote:

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

"Saintsman" wrote in message
...
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?

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

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



"Zone" wrote:

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

"Saintsman" wrote in message
...
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?

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

"Zone" wrote:

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

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



"Zone" wrote:

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

"Saintsman" wrote in message
...
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?

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

"Zone" wrote:

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

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



"Zone" wrote:

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

"Saintsman" wrote in message
...
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?

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

"Saintsman" wrote in message
...
OK - FOUND IT - USED CAPITALS IN CODE !! lower case on sheet!
I should know better...Doh!

"Zone" wrote:

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

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



"Zone" wrote:

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

"Saintsman" wrote in message
...
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?