Increment Cell Value & Print Sheet At Each Step

dhayes · Mar 13th 2008

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).

Simon Lloyd · Mar 13th 2008

Re: Increment Cell Value Then Print

Try this:

Code

Sub Increase_Cell_n_Print()
Dim i As Integer
For i = 0 To 220
Range("A1").Value = Range("A1").Value + 1
Activesheet.printout
Next i
End Sub

**Trebor76** · Mar 13th 2008

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

Code

Sub NoOfPrintOuts()


Application.ScreenUpdating = False


Dim intNoOfPrintOuts, intPrintCnt As Integer


intNoOfPrintOuts = Val(InputBox("How many ""NEW PART ENTRY"" print outs would you like?", "Print Out Editor", 1))
    
    'Exit the routine if zero is entered or Cancel is pressed.
    If intNoOfPrintOuts = 0 Then
    Exit Sub
    Else
    intPrintCnt = 1
        Do Until intPrintCnt = intNoOfPrintOuts
        ActiveWindow.SelectedSheets.PrintOut Copies:=intPrintCnt, Collate:=True
        intPrintCnt = intPrintCnt + 1
        Loop
    End If


Application.ScreenUpdating = True


End Sub

Display More

dhayes · Mar 13th 2008

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.

**Trebor76** · Mar 14th 2008

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

Code

Sub PrintSpecficItem()
    
    intItemNo = Val(InputBox("Enter the item no to be printed.", "Print Out Editor"))
     
     'Exit the routine if zero is entered or Cancel is pressed.
    If intItemNo = 0 Then
        Exit Sub
    Else
        Range("J2").Value = intItemNo
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If
        
End Sub

Display More

Simon Lloyd · Mar 14th 2008

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
 Dim ib As Variant
 ib = Application.InputBox("Enter amount of increments", "Cell Progression", , , , , 1)
 If Target <> Range("J2") Then Exit Sub
    For i = 0 To ib
         Range("J2").Value = Range("J2").Value + 1
        ActiveSheet.PrintOut
    Next i
End Sub

**Trebor76** · Mar 14th 2008

Re: Increment Cell Value & Print Sheet At Each Step

dhayes,

One more time for the dummies

Try this:

Code

Sub ItemPrintOptions()


Application.ScreenUpdating = False


Dim strResponse As String
Dim lngLastRow, lngPrintCnt As Long


strResponse = MsgBox("Select your desired print option:" & vbNewLine & _
"1. Print all products (Yes)" & vbNewLine & _
"2. Print a selected product (No)" & vbNewLine & _
"3. Do nothing (Cancel)", vbYesNoCancel, "Print Product(s) Editor")
 
Select Case strResponse
    Case vbYes
    lngLastRow = Sheets(5).Cells(Rows.Count, "A").End(xlUp).Row
    lngPrintCnt = 1


        Do Until lngPrintCnt = lngLastRow
            Sheets(6).Range("J2") = lngPrintCnt
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            lngPrintCnt = lngPrintCnt + 1
        Loop
    
    Case vbNo
    Sheets(6).Range("J2").Value = _
    Val(InputBox("Enter the product number you wish to print", "Print Product Editor"))    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Case vbCancel
    
End Select


Application.ScreenUpdating = True


End Sub

Display More

HTH

Robert

dhayes · Mar 14th 2008

Re: Increment Cell Value & 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.

**Trebor76** · Mar 16th 2008

Re: Increment Cell Value & 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

pedrooz · Apr 10th 2021

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

**royUK** · Apr 11th 2021

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.

Increment Cell Value & Print Sheet At Each Step

Participate now!

Share