Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community
SOLVED

# Print consecutive numbers from Excel

Copper Contributor

# Print consecutive numbers from Excel

I have a template that auto fills data on how each part is configured on a cart and tells how many carts are needed based on order qty and would like it to print the cart number on each sheet. If I need 4 carts I would like 4 sheets printed and in the Cart# cell, 1 for the first page printed 2 for the secong page printed and so on. Is there a way to do that?

6 Replies

# Re: Print consecutive numbers from Excel

See the attached version. It is a macro-enabled workbook so you'll have to allow macros when you open it.

# Re: Print consecutive numbers from Excel

Thank you Hans, that worked great. I was wondering if there was a way to look at cell O9 (Max layers per cart) and subtract that from cell P9 (total layers needed) for each cart needed. In this case I need 3.91 carts so cart 1 - 3 take 16 layers which leaves 14.5 layers for cart # 4. The number of layers need to display in cell B8. Cart1 -16 layers, cart 2 -16 layers, cart 3 -16 layers, cart 4 -14.5 layers when the print buttun you created is pressed.

Thank you!

# Re: Print consecutive numbers from Excel

See the attached version. I changed B8 from 0 to 1 decimal place.

# Re: Print consecutive numbers from Excel

Hans, again thank you for your help. I realized I did not account for scrap so I changed cell P9, the Total Layers needed to add in 10% scrap. I also had to change cell B6 to divide P9 by O9 and now it does not print correctly. It is only printing 2 sheets with 18.4 layers on the last sheet instead of 16 and then printing the 3rd sheet with 2.4, which is the balance. Can you help me on this? This should be my last correction. Thanks in advance.

best response confirmed by Scott Hetzel (Copper Contributor)
Solution

# Re: Print consecutive numbers from Excel

Try this version:

``````Sub PrintTags()
Dim n As Long
Dim i As Long
n = Application.RoundUp(Range("B6").Value, 0)
For i = 1 To n
Range("B18").Value = i
If i < n Then
Range("B8").Value = Range("O9").Value
Else
Range("B8").Value = Range("P9").Value - (n - 1) * Range("O9").Value
End If
ActiveSheet.PrintOut
Next i
Range("B8,B18").ClearContents
End Sub``````

# Re: Print consecutive numbers from Excel

Thank you very much for all of your help Hans. This works great.
1 best response

Accepted Solutions
best response confirmed by Scott Hetzel (Copper Contributor)
Solution

# Re: Print consecutive numbers from Excel

Try this version:

``````Sub PrintTags()
Dim n As Long
Dim i As Long
n = Application.RoundUp(Range("B6").Value, 0)
For i = 1 To n
Range("B18").Value = i
If i < n Then
Range("B8").Value = Range("O9").Value
Else
Range("B8").Value = Range("P9").Value - (n - 1) * Range("O9").Value
End If
ActiveSheet.PrintOut
Next i
Range("B8,B18").ClearContents
End Sub``````