Auto Increment Number on Data Capture Sheets but reverse print out

dinodude

New Member
Joined
Dec 14, 2017
Messages
7
After much searching i found a macro to incrementaly step an order code for our company data capture sheets:
Code:
Sub print_sheet()
P = InputBox("How many sheets?")
    For I = 1 To P Step 1
        Worksheets("Sheet1").PrintOut
        Range("C3").Value = Range("C3").Value + 1
    Next I
End Sub
but - if i want to do a large print run of say 200 sheets, it would be beneficial to have the sheets printed in reverse order but keep the number increment in the correct order....
Is this possible please ?

Any help would be really helpful, Thanks
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,748
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board
Maybe
Code:
Sub print_sheet()
P = InputBox("How many sheets?")
    For i = P To 1 Step -1
        Worksheets("Sheet1").PrintOut
        Range("C3").Value = Range("C3").Value - 1
    Next i
End Sub
 

dinodude

New Member
Joined
Dec 14, 2017
Messages
7
Hi Fluff

That did work by printing in reverse order 7.6.5.4.3.2.1 but it also increments in reverse order 7,6,5,4,3,2,1

I need it to increment 1,2,3,4,5,6,7 but print 7.6.5.4.3.2.1

Many Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,748
Office Version
  1. 365
Platform
  1. Windows
In that case simply change the C3 to +1 from -1
 

dinodude

New Member
Joined
Dec 14, 2017
Messages
7

ADVERTISEMENT

In that case simply change the C3 to +1 from -1

tried that and it still prints increment up

also tried to change step from -1 to 1 and the macro died

:(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,748
Office Version
  1. 365
Platform
  1. Windows
Is C3 the print number?
 

dinodude

New Member
Joined
Dec 14, 2017
Messages
7

ADVERTISEMENT

Hi Fluff

C3 is the cell the number resides in

Snap1.png


this is so each individual data capture sheet has a unique number that cab be easily read, as previously we had duplicate and triplicate numbers when they were hand written
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,748
Office Version
  1. 365
Platform
  1. Windows
So you want the first sheet out to say 17315, then 17314 etc, but at the end that cell should read 17315 Is that right?
 

dinodude

New Member
Joined
Dec 14, 2017
Messages
7
Hi Fluff

So you want the first sheet out to say 17315, then 17314 etc, but at the end that cell should read 17315 Is that right?

Yes, when you click my macro and run you get a pop up asking how many sheets, if I add "5" it will step to 17315
the sheets will print - 17310, 17311, 17312, 17313, 17314

if I add 100 it will step accordingly - which if I didn't want to see the number of the first sheet I picked up I could leave the sheets face down, but that's not practical.

So in the scenario above we would need the sheets to increment to 17315 but the last printed sheet would be the starting number of17310
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,748
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
   P = InputBox("How many sheets?")
   Range("C3").Value = Range("C3").Value + P
   For i = P To 1 Step -1
      Worksheets("Sheet1").PrintOut
      Range("C3").Value = Range("C3").Value - 1
   Next i
   Range("C3").Value = Range("C3").Value + P
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top