Print Excel worksheet several times, looping value of 1 cell
 

I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

Print Excel worksheet several times, looping value of 1 cell
 

Manual there is no option for this

For code see this page to print the activesheet
http://www.rondebruin.nl/print.htm#number

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hershmab" wrote in message ...
I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

Print Excel worksheet several times, looping value of 1 cell
 

Sub printCopies()
Dim myCell As String

myCell = Range("A1").Value
ActiveWindow.SelectedSheets.PrintOut _
Copies:=myCell, Collate:=True
End Sub

"Hershmab" wrote:

I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

Print Excel worksheet several times, looping value of 1 cell
 

Sorry, I left out some critical information so that both replies, yours and
"Mike"'s, do not solve my problem:
The whole of the worksheet consists of formulae dependent on the value of
the "looping" cell.
So the cell must change its value and the worksheet must be recalculated
every time before it is printed.
The loop will always start at 1, but the maximum will vary from time to
time. I could put the current maximum into another cell in the worksheet

"Ron de Bruin" wrote:

Manual there is no option for this

For code see this page to print the activesheet
http://www.rondebruin.nl/print.htm#number

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hershmab" wrote in message ...
I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

Print Excel worksheet several times, looping value of 1 cell
 

You can try this

It change the value of A1 ten times and print (1-10)

Sub test()
Dim num As Long

For num = 1 To 10
With ActiveSheet

.Range("a1").Value = num

.Calculate

'Print the sheet
.PrintOut
End With
Next num

End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hershmab" wrote in message ...
Sorry, I left out some critical information so that both replies, yours and
"Mike"'s, do not solve my problem:
The whole of the worksheet consists of formulae dependent on the value of
the "looping" cell.
So the cell must change its value and the worksheet must be recalculated
every time before it is printed.
The loop will always start at 1, but the maximum will vary from time to
time. I could put the current maximum into another cell in the worksheet

"Ron de Bruin" wrote:

Manual there is no option for this

For code see this page to print the activesheet
http://www.rondebruin.nl/print.htm#number

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hershmab" wrote in message ...
I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

Print Excel worksheet several times, looping value of 1 cell
 

On Jul 14, 9:17 am, "Ron de Bruin" wrote:
You can try this

It change the value of A1 ten times and print (1-10)

Sub test()
Dim num As Long

For num = 1 To 10
With ActiveSheet

.Range("a1").Value = num

.Calculate

'Print the sheet
.PrintOut
End With
Next num

End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Hershmab" wrote in ...
Sorry, I left out some critical information so that both replies, yours and
"Mike"'s, do not solve my problem:
The whole of the worksheet consists of formulae dependent on the value of
the "looping" cell.
So the cell must change its value and the worksheet must be recalculated
every time before it is printed.
The loop will always start at 1, but the maximum will vary from time to
time. I could put the current maximum into another cell in the worksheet

"Ron de Bruin" wrote:

Manual there is no option for this

For code see this page to print the activesheet
http://www.rondebruin.nl/print.htm#number

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Hershmab" wrote in ...
I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)

To make it flexible to print the number of times you specify in a cell
(say B1), you need to pull the value of B1 in as a variable, which is
simply

Dim nloops as Long
nloops = Range("B1").Value

For num = 1 to nloops
..
..
..

When I move variables on or off the spreadsheet I prefer to define and
use named ranges. Name A1 "Target" and B1 "NumLoops" for example, and
use:

Range("Target").Value = num

and

nloops = Range("NumLoops").Value

Print Excel worksheet several times, looping value of 1 cell
 

Thanks to all three of you who replied, so helpfully. I have one more
question about this topic:

At the moment Excel sends each report/copy to the printer individually;
is there any way to send them all together as a single multi-sheet printout?

"Mike" wrote:

Sub printCopies()
Dim myCell As String

myCell = Range("A1").Value
ActiveWindow.SelectedSheets.PrintOut _
Copies:=myCell, Collate:=True
End Sub

"Hershmab" wrote:

I need to print a worksheet several times, using the value of 1 cell as an
automatic loop-counter from 1 to, say, 10.
1. Can it be done without a macro?
2. If not, what is the VBA code for a suitable macro? (I have a lot of
programming experience, but hardly any VBA!)