Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP

Thanks, I will try that!
Anne

"Gord Dibben" wrote:

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP

Gord, when I try this--thanks for the code--I get one sheet printed 150 times
(actually, I set it to 5 pages to test)... and each sheet says page 1 of 1.

I need each sheet to say 1 of 5, 2 of 5, 3 of 5... does that make sense? Any
help you could provide on this would be great.
Anne

"Gord Dibben" wrote:

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP

So you have data on one worksheet that prints on one sheet of paper.

And you want to print that 150 times and each printed sheet should look the same
except for the footer that should say 1 of 150, 2 of 150, ..., 150 of 150?

Option Explicit
Sub PrintCopies()

Dim wks As Worksheet
Dim iCtr As Long
Dim HowMany As Long

Set wks = ActiveSheet

HowMany = 5 '150 after testing

With wks
For iCtr = 1 To HowMany
.PageSetup.CenterFooter = "Page " & iCtr & " of " & HowMany
'only the first page from that worksheet
.PrintOut from:=1, to:=1, preview:=True
Next iCtr
End With
End Sub

Preview:=true will save paper while testing.


Anne wrote:

Gord, when I try this--thanks for the code--I get one sheet printed 150 times
(actually, I set it to 5 pages to test)... and each sheet says page 1 of 1.

I need each sheet to say 1 of 5, 2 of 5, 3 of 5... does that make sense? Any
help you could provide on this would be great.
Anne

"Gord Dibben" wrote:

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP


--

Dave Peterson

Thanks, Dave! This will work!!

"Dave Peterson" wrote:

So you have data on one worksheet that prints on one sheet of paper.

And you want to print that 150 times and each printed sheet should look the same
except for the footer that should say 1 of 150, 2 of 150, ..., 150 of 150?

Option Explicit
Sub PrintCopies()

Dim wks As Worksheet
Dim iCtr As Long
Dim HowMany As Long

Set wks = ActiveSheet

HowMany = 5 '150 after testing

With wks
For iCtr = 1 To HowMany
.PageSetup.CenterFooter = "Page " & iCtr & " of " & HowMany
'only the first page from that worksheet
.PrintOut from:=1, to:=1, preview:=True
Next iCtr
End With
End Sub

Preview:=true will save paper while testing.


Anne wrote:

Gord, when I try this--thanks for the code--I get one sheet printed 150 times
(actually, I set it to 5 pages to test)... and each sheet says page 1 of 1.

I need each sheet to say 1 of 5, 2 of 5, 3 of 5... does that make sense? Any
help you could provide on this would be great.
Anne

"Gord Dibben" wrote:

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP


--

Dave Peterson

Anne

My mistake......bad code. Lack of thought.

See Ron de Bruin's site for good code with more thought input.

http://www.rondebruin.nl/print.htm#number

Note: Ron's code is set to print with the value in a cell.

Add an apostrophe to that line and clear the apsostrophe from the
..PageSetup.LeftFooter line.



Gord


On Mon, 18 Dec 2006 12:25:06 -0800, Anne wrote:

Gord, when I try this--thanks for the code--I get one sheet printed 150 times
(actually, I set it to 5 pages to test)... and each sheet says page 1 of 1.

I need each sheet to say 1 of 5, 2 of 5, 3 of 5... does that make sense? Any
help you could provide on this would be great.
Anne

"Gord Dibben" wrote:

Anne

You want 150 printed copies of one worksheet and you have already set the Footer
as Page N of NN?

This macro will print 150 copies of a sheet.

Sub PrintCopies()
Dim i As Long
For i = 1 To 150
ActiveSheet.PrintOut
Next i
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Tue, 12 Dec 2006 08:28:02 -0800, Anne wrote:

Hello!
I would like to create a log book, with Excel. I would like to have one
sheet repeated over and over, for about 150 pages, with page N of NN as the
footer. I know how to do the footer, but does anyone have ideas on how to
create the duplicate pages for the log book?
Thanks
Anne

Gord Dibben MS Excel MVP