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

<div class="code-mirror  language-javascript" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;">Sub <span class="token function">IncrementPrint</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
'updateby Nirmal
   Dim xCount As Variant
   Dim xScreen As Boolean
   Dim <span class="token constant">I</span> As Long
   On Error Resume Next
LInput<span class="token operator">:</span>
   xCount <span class="token operator">=</span> Application<span class="token punctuation">.</span><span class="token function">InputBox</span><span class="token punctuation">(</span><span class="token string">"Please enter the number of copies you want to print:"</span><span class="token punctuation">,</span> <span class="token string">"no of prints required"</span><span class="token punctuation">)</span>
   If <span class="token function">TypeName</span><span class="token punctuation">(</span>xCount<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">"Boolean"</span> Then Exit Sub
   <span class="token function">If</span> <span class="token punctuation">(</span>xCount <span class="token operator">=</span> <span class="token string">""</span><span class="token punctuation">)</span> <span class="token function">Or</span> <span class="token punctuation">(</span>Not <span class="token function">IsNumeric</span><span class="token punctuation">(</span>xCount<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token function">Or</span> <span class="token punctuation">(</span>xCount <span class="token operator"><</span> <span class="token number">1</span><span class="token punctuation">)</span> Then
      MsgBox <span class="token string">"error occured, please check value"</span><span class="token punctuation">,</span> vbInformation<span class="token punctuation">,</span> <span class="token string">"no of prints required"</span>
      GoTo LInput
   Else
      xScreen <span class="token operator">=</span> Application<span class="token punctuation">.</span>ScreenUpdating
      Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> False
      For <span class="token constant">I</span> <span class="token operator">=</span> <span class="token number">1</span> To xCount
         ActiveSheet<span class="token punctuation">.</span><span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A1"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Value <span class="token operator">=</span> <span class="token string">"Print-1"</span> <span class="token operator">&</span> <span class="token constant">I</span>
         ActiveSheet<span class="token punctuation">.</span>PrintOut
      Next
      ActiveSheet<span class="token punctuation">.</span><span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A1"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>ClearContents
      Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> xScreen
   End If
End Sub
</div>

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.