Domenic, where are you?

[Sandra]

Domenic,
I have a problem I know you can help me with.

I need a macro that will ask how many copies to print,
then add one to a cell for each copy.

Thanks,
Sandra, your faithful student

 

[Ron de Bruin]

Domenic, where are you?

I don't know???

This example will print ? copies of the same sheet (It use a Input box to ask you how many)
It will copy the page number in cell A1or in the Header or Footer.

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell A1
.Range("a1").Value = CopieNumber & " of " & CopiesCount

'number in the footer
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
End With
Next CopieNumber
End Sub

 

[Sandra]

Thank you, Ron.

Could you please tell me what does the "As Long" mean?
What is the difference in "As Long" and "As Integer"?

-----Original Message-----

I don't know???

This example will print ? copies of the same sheet (It

use a Input box to ask you how many)

It will copy the page number in cell A1or in the Header or Footer.

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell A1
.Range("a1").Value = CopieNumber & " of " & CopiesCount

'number in the footer
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
End With
Next CopieNumber
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Sandra

You can use a integer also if you not want to print more then 32767 pages <g>

From the VBA help

Long (long integer) variables are stored as signed 32-bit (4-byte)
numbers ranging in value from -2,147,483,648 to 2,147,483,647.
The type-declaration character for Long is the ampersand (&).

Integer variables are stored as 16-bit (2-byte) numbers ranging in
value from -32,768 to 32,767. The type-declaration character for
Integer is the percent sign (%).

 

[Sandra]

Ron,

Thanks for explaining that.
Now, another problem:

I need the copy number to keep incrementing, not start
over at each printing.
I substituted this:
Range("E5").Value = Range("D5").Value + 1
for your 8th line (beginning with "Range".

But when I try to run the macro I get the error message
type mismatch.
Can you help me again?

-----Original Message-----
Hi Sandra

You can use a integer also if you not want to print more

From the VBA help

Long (long integer) variables are stored as signed 32- bit (4-byte)
numbers ranging in value from -2,147,483,648 to 2,147,483,647.
The type-declaration character for Long is the ampersand (&).

Integer variables are stored as 16-bit (2-byte) numbers ranging in
value from -32,768 to 32,767. The type-declaration character for
Integer is the percent sign (%).

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Try this

I use E5

Is it a typo that you use E5 and D5?

Range("E5").Value = Range("D5").Value + 1

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

With ActiveSheet
For CopieNumber = .Range("E5").Value To CopiesCount + .Range("E5").Value

'number in cell A1
.Range("E5").Value = CopieNumber

'Print the sheet
.PrintPreview
Next CopieNumber
End With
End Sub

 

[Ron de Bruin]

Oops

Change printpreview to printout

 

[Sandra]

When I paste the code, everything underneath the Sub...
is in red type. Then when I try to run it I get the
error, "Compile Error" "Syntax Error".

I went back to the original, and I got the same error
message.

I shut down my computer, restarted, and the first worked,
but not the second.

Restarted again, and I get the same error message every
time.

Any ideas?

-----Original Message-----
Oops

Change printpreview to printout

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Sandra

If your code lines are red then line wrap is the problem

Copy and paste this one


Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox _
("How many Copies do you want", Type:=1)

With ActiveSheet
For CopieNumber = .Range("E5").Value _
To CopiesCount + .Range("E5").Value

'number in cell A1
.Range("E5").Value = CopieNumber

'Print the sheet
.PrintOut
Next CopieNumber
End With
End Sub

 

[Sandra]

That gave me the error: Runtime error '13' Type Mismatch

Thanks. Don't give up on me.

-----Original Message-----
Hi Sandra

If your code lines are red then line wrap is the problem

Copy and paste this one


Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox _
("How many Copies do you want", Type:=1)

With ActiveSheet
For CopieNumber = .Range("E5").Value _
To CopiesCount + .Range("E5").Value

'number in cell A1
.Range("E5").Value = CopieNumber

'Print the sheet
.PrintOut
Next CopieNumber
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Sandra

Send me your test workbook(private) and I will look at it.

 

[Sandra]

How do I do that?

-----Original Message-----
Hi Sandra

Send me your test workbook(private) and I will look at it.

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Send the workbook to this address

(e-mail address removed)

 

[Sandra]

I found my problem! I had a string in the cell where I
wanted to add a 1.
But what does the "Type:=1" mean in the following
statement?

("How many Copies do you want", Type:=1)

Thank you!

-----Original Message-----
Send the workbook to this address

(e-mail address removed)


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Sandra

From the VBA help (Inputbox)

Type Optional Variant. Specifies the return data type.
If this argument is omitted, the dialog box returns text.

Can be one or a sum of the following values.

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values


You can use the sum of the allowable values for Type.
For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

 

[Sandra]

Thank you, Ron. You've been a great help!

-----Original Message-----
Hi Sandra

From the VBA help (Inputbox)

Type Optional Variant. Specifies the return data type.
If this argument is omitted, the dialog box returns text.

Can be one or a sum of the following values.

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values


You can use the sum of the allowable values for Type.
For example, for an input box that can accept both text

and numbers, set Type to 1 + 2.

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sandra" <[email protected]> wrote in

message news:[email protected]...