Increment Cell Value & Print Sheet At Each Step

  • I am trying to automatically increment a cell value then print the sheet. I need it to increment up to 220, or possible higher, and then print at each increment; 1,2,3,etc. as opposed to typing 1 then print, 2 then print, 3 then print...etc. Can this be done with a macro? I've attached a file if you want to view it (cell J2 of "NEW PART ENTRY" sheet).

  • Re: Increment Cell Value Then Print


    Try this:

    Code
    1. Sub Increase_Cell_n_Print()
    2. Dim i As Integer
    3. For i = 0 To 220
    4. Range("A1").Value = Range("A1").Value + 1
    5. Activesheet.printout
    6. Next i
    7. End Sub
  • Re: Increment Cell Value Then Print


    dhayes,


    You could use the following where you initially enter how many printouts you need into the "Print Out Editor" Inputbox and then press OK.


    HTH


    Robert


  • Re: Increment Cell Value Then Print


    Thank you for the replies.


    Simon,


    Unfortunately I got a run-time error '13'. I tried changing the "A1" to "J2" but then it just printed the same page over and over (I changed the amount of prints to 5 for testing).


    Trebor,


    It worked but did the same thing as Simon's. It just prints the same page.


    I may not have explained what I wanted to do clearly or I may be doing something wrong. I am VERY new to VB. Nontheless if you look at sheet "NEW PART ENTRY" in cell "J2", when you change that number (from 1 to 2 to 3 to 4..etc) the information within the sheet will changed based on what number you enter. It reads the information from sheet "ALL" and replaces it with the new information. So if I type in 5 or 6 or whatever I get a new part number entered and a new description.


    Let me know if this helps or if I am just not using the program right. Thanks again guys. I really appreciate the help.

  • Re: Increment Cell Value Then Print


    dhayes,


    Ah, sorry about that I missed the point there. Let me know if the following is what you're after.


    HTH


    Robert


  • Re: Increment Cell Value & Print Sheet At Each Step


    Perhaps you need it in the worksheet module (ALt+F11, then double click the name of the worksheet you want on the left hand side)

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Dim i As Integer
    3. Dim ib As Variant
    4. ib = Application.InputBox("Enter amount of increments", "Cell Progression", , , , , 1)
    5. If Target <> Range("J2") Then Exit Sub
    6. For i = 0 To ib
    7. Range("J2").Value = Range("J2").Value + 1
    8. ActiveSheet.PrintOut
    9. Next i
    10. End Sub
  • Re: Increment Cell Value &amp; Print Sheet At Each Step


    dhayes,


    One more time for the dummies :thumbup:


    Try this:



    HTH


    Robert

  • Re: Increment Cell Value &amp; Print Sheet At Each Step


    Thanks guys. I really appreciate the support. Trebor, the last one is exactlty what I needed. It doesn't seem to want to print every number though, for some reason. For example, lets say I have sheets 1-5 to print it only prints 1-4. I don't understand why but I can just add an extra number to it and it'll print everything ok, but I was just curious as to why it stopped short by 1 print.


    Also, how would I go about putting a "CommandButton" to do this action, as opposed to, Tools/Macro/Run..etc. ? I can get a command button up but I don't know how to assign an actual task to it. Thanks again guys. All the help is appreciated.

  • Re: Increment Cell Value &amp; Print Sheet At Each Step


    dhayes,


    I've amended the code on the attached and put a button on the "NEW PART ENTRY" tab to run it - let me know if there are any problems.


    FYI - follow these three steps to assign a macro to a command button:


    1. Right click the button and from the shortcut menu click Assign Macro
    2. Click of the relevant macro from the Macro Name listbox
    3. Click OK


    HTH


    Robert

  • can anyone create a macro that increases cell "A1" value (001) each time i modify the file and print it.


    like when i print it for the first time it would be "001" , then when i modify some thing in the file and print it a second time it would be "002" and so on in each time it i modify it and print it , it would be incremented by one.


    thank you beautiful people

  • Welcome to the Forum


    Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

  • royUK

    Closed the thread.