Increment and print macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:43 PM GMT

Question

QUESTION: I have an2007 file that contains a parking permit. Each permit must have a unique 5-digit number which is in cell "E1". The file is intended to be saved in on a server so that each department can print out the permit. Each time a permit is printed i need "E1" to increase by 1. I have tried the following macro but the number resets each time a permit is printed. I do not need to be able to print multiple pages at once as the macro does, but it would be useful.

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

For CopieNumber = "E1" + 1 To CopiesCount With ActiveSheet 'number in cell E1 (prints "n") .Range("E1").Value = CopieNumber

'number in cell E1 (prints "n of z") '.Range("E1").Value = CopieNumber & " of " & CopiesCount

'number in the footer (prints "n of z") '.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

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

Any help would be greatly appreciated

ANSWER: well there are a number of things I don't understand about your code.

First, I can't even run it. I get "type mismatch" on this line: For CopieNumber = "E1" + 1 To CopiesCount

Which is because "E1" doesn't mean anything. So I changed that line to For CopieNumber = Range("E1").Value + 1 To CopiesCount

and at least it runs.

But then also there are several lines of your code that are commented. This means they aren't running. Do you want them this way???

Try un-commenting this line '.Range("E1").Value = CopieNumber & " of " & CopiesCount

and this line '.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

Lastly, add this line of code directly before the END SUB line Range("E1").Value = Range("e1").Value + CopiesCount

---------- FOLLOW-UP ----------

QUESTION: Ok I have updated the code with your changes. It now looks like this:

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

For CopieNumber = Range("E1").Value + 1 To CopiesCount With ActiveSheet 'number in cell E1 (prints "n") .Range("E1").Value = CopieNumber

'number in cell E1 (prints "n of z") .Range("E1").Value = CopieNumber & " of " & CopiesCount

'number in the footer (prints "n of z") .PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet .PrintOut End With Next CopieNumber Range("E1").Value = Range("e1").Value + CopiesCount End Sub


It no longer prints the page, it just increases the number. What am I missing?

ANSWER: It totally prints for me ... I ran the sub, entered 5, and it printed out 5 copies.

Double check your printer connection, default printer, etc? That's all I can think of, it works for me.

Only other thing I can think of is test your basic printing function by coding something else simple.

Go to a worksheet that has at least some data on it.

try

Sub Test() Worksheets("Sheet1").Printout 'change sheet name End Sub

make sure something definitely comes out of your printer :)

---------- FOLLOW-UP ----------

QUESTION: OK. I discovered the cause of why it didn't print. However this creates a new problem. I had a value "90001" in cell E1. When I removed this value it prints, but prints the "1 of 1" when I print one copy. What I need is to start with a value of "90000" and have it increment and print from that number, 90001, 90002, etc. What do I need to change in the code?

I appreciate all of your help thus far.

Answer

Ok, so I think the problem is these lines:

For CopieNumber = Range("E1").Value + 1 To CopiesCount

That's basically what you're saying, right? This is tellingto print, as many times as there are numbers in between 90000 and the user's input box response. Since there aren't any numbers advancing incrementally between 90000 and 1, it prints nothing.

How about saying For CopieNumber = 1 To CopiesCount that literally means, if the user says 5, then print 1-5 (5 times).

I guess what's confusing me is the original design of this code , it just confuses me.

Why are these lines:

'number in cell E1 (prints "n") .Range("E1").Value = CopieNumber

'number in cell E1 (prints "n of z") .Range("E1").Value = CopieNumber & " of " & CopiesCount

so you make the value of E1 one thing, then immediately change it to something else? I don't see the purpose of the first part of it, even with the comment.

Also, it sounds like we need to just flat out utilize another variable. To remember what E1 was when we started, and what it needs to change back to when we're finished.

I re-wrote this thing and I tested it and it seems to work great.

If you use this code, start out with 90000. Run 2 copies. It will print 2 copies correctly, each one saying like, 90001 of 90002, and then at the end, E1 is 90002. so that the macro knows what to do next time.

Sub PrintCopies_ActiveSheet()

Dim CopiesCount As Long Dim copienumber As Long Dim PreviousState As Long PreviousState = Range("E1").Value

CopiesCount = Application.InputBox("How many Copies do you want?", Type:=1)

For copienumber = 1 To CopiesCount With ActiveSheet

.Range("E1").Value = PreviousState + copienumber & " of " & PreviousState + CopiesCount 'so if you started with 90000, and user chose 4 copies, this should now be this logic: 'New value of E1, if you were on the 3rd copy, is: "90003 of 90004

'number in the footer (prints "n of z") .PageSetup.LeftFooter = copienumber & " of " & CopiesCount

'Print the sheet .PrintOut End With Next copienumber Range("E1").Value = PreviousState + CopiesCount End Sub

Advertisement

©2021 eLuminary LLC. All rights reserved.