Excel VBA with Counter

[seema290]

Im trying to create a VBA that reads in a certain cell value (any number from 1 to 10 depending on the input from the user) and then changes to value to read "1 of 10" and prints out a sheet. Then the value increases to read "2 of 10" and prints out again, this needs to continue until all the sheets are printed out ("10 of 10").
This could be different if the user inputs a different value though, for example, 2. I would need it to print out two sheets that say 1 of 2 and then 2 of 2

 

[pvans267]

Hi Seema290,

Not sure if I completely understand your question, but here are some options:

A simple Counter in VBA that takes the value the user entered in cell A1 and then changes the cell value to "1 of ..." and loops until it reaches the original value stored in A1.
Sub TestPrint()

intNumberOfSheets = Worksheets("Sheet1").Range("A1")

For Sheet = 1 To intNumberOfSheets
Worksheets("Sheet1").Range("A1") = Sheet & " of " intNumberOfSheets
MsgBox Sheet & " of " intNumberOfSheets
Next Sheet

End Sub

Alternatively, if you actually want to print the sheet, perhaps take a look at this article from the Rondebruin website:
"Print the same worksheet ? time with the number in cell, header or footer"
Link: https://www.rondebruin.nl/win/s9/win010.htm

Should achieve exactly as you require with slight modifications.

Hope that helps

 

[seema290]

Hi,

I tried something similar to that but it is still not working, Im not sure if I have the counter set up correctly though

 

[pvans267]

Hi,

I tried something similar to that but it is still not working, Im not sure if I have the counter set up correctly though

Is it possible for you to share the file (or even just the code if necessary) and I could take a look? Not sure how to help otherwise