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

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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

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" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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,

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

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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

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
---------------
Jacob Skaria


"Bryan" wrote:

Jacob,

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

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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

Perfect!! Thank you!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bryan" wrote:

Jacob,

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

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Bryan" wrote:

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