Macro to fill in 1 of 10

[Bryan]

Sorry, I did not know how to word that title. What I need is a macro that
will fill in 1, 2, 3,... of however many the user requires. If there are 10
copies required, then the first page would print out as 1 of 10, the second
as 2 of 10, etc. I also need to fill in the PPO number on each printed
sheet, (same for all of that set). I do this in word, but am unfamiliar with
how to do this in excel. In Word, I use an autoopen macro that pops up
messageboxes for data entry. It then sends to my default printer.

thanx,
Bryan

 

[Jacob Skaria]

Bryan, try the below macro. I am not sure in which cell do you have the PPO
number...I have mentioned that as cell B2. Change to suit your requirement.
Also I assume this is a one paged template which you are trying to print..Try
and feedback

Sub Macro1()
intCopies = InputBox("Number of copies to print")
varPPO = InputBox("Enter the PPO number")
Range("B2") = intPPO
For intTemp = 1 To intCopies
ActiveSheet.PageSetup.RightFooter = intTemp & " of " & intCopies
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Next
End Sub

 

[Bryan]

Thanks for the prompt reply Jacob! I have a couple of problems here.
1 - is this placed in the Sheet1 or ThisWorkbook? (There is only 1 sheet)
2 - Unless I've missed something, this never prompts the user for input. I
tried this in both of the above, but it just opened the sheet and that was
that.
3 - Can the 1 0f 10 be placed in a cell instead of the footer?

Thanx,
Bryan

 

[Jacob Skaria]

Bryan

Set the Security level to low/medium in (Tools|Macro|Security). Open the
workbook. From workbook launch VBE using short-key Alt+F11. From menu
'Insert' a module and paste the below code. Save. Get back to Workbook. Run
macro from Tools|Macro|Run <selected macro()>

Adjust the range Range("A10") where the page numbers are printed
Adjust the range Range("B2")

Sub Macro1()
intCopies = InputBox("Number of copies to print")
varPPO = InputBox("Enter the PPO number")
Range("B2") = varPPO
For intTemp = 1 To intCopies
Range("A10") = intTemp & " of " & intCopies
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Next
End Sub

 

[Bryan]

Jacob,

Works great. Can this macro be made to run on open instead of manually
running it?

 

[Jacob Skaria]

Place that in Workbook_Open event...From VBE double click 'This Workbook and
paste the code and try

Private Sub Workbook_Open()
intCopies = InputBox("Number of copies to print")
If intCopies > 0 Then
varPPO = InputBox("Enter the PPO number")
Range("B2") = varPPO
For intTemp = 1 To intCopies
Range("A10") = intTemp & " of " & intCopies
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Next
End If
End Sub

If this post helps click Yes

 

[Bryan]

Perfect!! Thank you!!

Place that in Workbook_Open event...From VBE double click 'This Workbook and
paste the code and try

Private Sub Workbook_Open()
intCopies = InputBox("Number of copies to print")
If intCopies > 0 Then
varPPO = InputBox("Enter the PPO number")
Range("B2") = varPPO
For intTemp = 1 To intCopies
Range("A10") = intTemp & " of " & intCopies
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Next
End If
End Sub

If this post helps click Yes