Print Quantity from a cell

[Joe@excel]

I have created an Invoice form in a tab sheet call Invoices In excel.

in this tab sheet "INVOICES" i have 30 bookmarks to separate pe
pages

example
D1
D2
D3
D4
D5
then there's a bookmark and continue to 2nd invoice
D6
D7
D8
D9
D10
then there's a bookmark and continue to 3rd invoice

and so on untill theres 30 invoices in the sheet. now when there's dat
entered in let say D1 then E1 shows 1 as a result using th
If(D1="","",1) and same for
D6 if there's data entered in it. the E6 shows 1 as a result usin
If(D6="","",1). this allows me to find out how many invoices i have t
print. by adding all the E column. on F1 using formula =sum(E1:E300).

not all 30 invoices have data. but if i just press print then all o
them print.

there for i will have to enter Crtl+P to get to print menu and the
select From and To to get the number shown in F1.

is there a way to create a *macro* or a *code* that will print an
given number in F1 as the total invoices to print?

once again thank you very very much...

 

[Barb Reinhardt]

Are you saying you want to be able to print multiple copies of all
worksheets? If so, there's an option on the Print Menu to print the number
of copies.

If that's not what you mean, come back and clarify your question.

 

[Dave Peterson]

Bookmark = pagebreak????
(not really important).

How about running a dedicated macro to print that page. You could even add a
button from the forms toolbar and assign this macro to this button. (Or just
run it via tools|macro|macros...)

Option Explicit
Sub PrintMe()

Dim HowMany As Range
With Worksheets("Invoices")
Set HowMany = .Range("f1")
If IsNumeric(HowMany) Then
.PrintOut from:=1, to:=HowMany.Value, preview:=True
Else
MsgBox "Value in: " & HowMany.Address(0, 0) & " isn't numeric!"
End If
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

ps. I used "preview:=true" for testing.

Bookmark = pagebreak????
(not really important).

How about running a dedicated macro to print that page. You could even add a
button from the forms toolbar and assign this macro to this button. (Or just
run it via tools|macro|macros...)

Option Explicit
Sub PrintMe()

Dim HowMany As Range
With Worksheets("Invoices")
Set HowMany = .Range("f1")
If IsNumeric(HowMany) Then
.PrintOut from:=1, to:=HowMany.Value, preview:=True
Else
MsgBox "Value in: " & HowMany.Address(0, 0) & " isn't numeric!"
End If
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Joe@excel]

Mr. Dave Peterson

You are the man... you did it... thank you so much.....

 

[Ken Wright]

Just a thought but you could have your formula in E1 that returns 1 and then
in E2/E3/E4/E5 etc have =E1/=E2/=E3/=E4 repeating this pattern down your
sheet.

You can then just set the print range to the entire range and by filtering
on 1 in Col E can just hit print without worrying about setting numbers.
Even easier to throw in a bit of code that you hit a button, it filters /
prints / unfilters and you are done.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------