How to Auto-Increment Cell Value in Excel after Each Printing?

Let us assume we have an Excel sheet that needs to be printed multiple times, and you want to know how many times the sheet is printed. There is no way in which we can solve this in Excel, but this can be solved using the VBA application. Read this tutorial to learn how you can automatically increment cell values in Excel after each printing.

Auto-Increment Cell Value after Each Printing

Here we will first insert a VBA module and then run it to complete its task. Let us see a simple process to understand how we can increment cell values after each printing.

Step 1

Let us consider an Excel sheet that contains data you want to print and also contains a column of numbers, as shown in the below image.

Now right-click on the sheet name and select view code to open the VBA application, then click on insert, select module, and type the programme into the text box as shown in the below image.

Example

Sub IncrementPrint()
'updateby Nirmal
   Dim xCount As Variant
   Dim xScreen As Boolean
   Dim I As Long
   On Error Resume Next
LInput:
   xCount = Application.InputBox("Please enter the number of copies you want to print:", "no of prints required")
   If TypeName(xCount) = "Boolean" Then Exit Sub
   If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
      MsgBox "error occured, please check value", vbInformation, "no of prints required"
      GoTo LInput
   Else
      xScreen = Application.ScreenUpdating
      Application.ScreenUpdating = False
      For I = 1 To xCount
         ActiveSheet.Range("A1").Value = "Print-1" & I
         ActiveSheet.PrintOut
      Next
      ActiveSheet.Range("A1").ClearContents
      Application.ScreenUpdating = xScreen
   End If
End Sub

Step 2

Now save the sheet as a macro-enabled template and click on F5 to run the code. A pop-up window will be generated as shown in the below image.

Step 3

The data in the cell is deleted after each print, and the pointer is moved to the cell below, as shown in the image below.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can increment cell values in Excel after each printing.

Pradeep Kumar

Updated on: 10-Jan-2023

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started