How to have a cell show quantity like 1 of 3, 2 of 3, etc...

[nmccracken12]

I am trying to build an excel template to create labels for cases. I have a cell that will be for quantity. Is there a way to print 2+pages and have that cell display ... 1 of 2 , 2 of 2 and so forth? The number in the cell would be totally based on the number of print jobs you ask it to do. If that is not doable, is there an alternative? Thanks in advance!

 

[Trebor76]

Hi nmccracken12,

If I've understood correctly here's a macro that will do what you're after:

Option Explicit
Sub Macro1()

    Dim lngQty As Long, i As Long
    Dim rngSrc As Range
    
    Application.ScreenUpdating = False
    
    Set rngSrc = ThisWorkbook.Sheets("Sheet1").Range("A1") '<-Sheet name and cell reference where the quantity amount number resides. Change to suit.
    lngQty = rngSrc.Value
    
    For i = 1 To lngQty
        rngSrc = i & " to " & lngQty
        rngSrc.Worksheet.PrintOut
    Next i
    
    rngSrc.Value = lngQty '<-Reset the source cell to the original quantity amount.
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[nmccracken12]

Thank you for the help! Maybe I just do not know what I am doing, but I keep getting errors. I think maybe it has to do with the sheet name? The sheet is named from what I can see Sheet 1... That is what I see under VBA Project, under Microsoft Excel Object. Underneath Sheet 1 it says ThisWorkbook... I have tried ThisWorkbook, Sheet 1, CGPO Label.

Any ideas? Super confused.

 

[Trebor76]

Sheet1 (no space) is the code name of the sheet but Sheet 1 (with space) is the tab name you see. Add a space between the t and 1 and all should be good.

 

[nmccracken12]

Thanks again! I got it to take the macro, but it is still not doing what I am needing. When I print this sheet, I am wanting cell E3 (its a merged cell if that matters, but that is the cell name that populates when clicking on it) to show the print quantity. So if I print 3 sheets: the first would say 1 of 3, the second 2 of 3, and the third 3 of 3. This is printing just a zero. I put another number in there and it just prints that number.

 

[nmccracken12]

I figured it out!!! Thank you!

 

[Trebor76]

I figured it out!!! Thank you!

Thanks for letting know and your welcome