Excel macro for numbering

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

I'm trying to find a macro that will allow me to put a number in cell A1 of a spreadsheet and when printed incrementtaht value by one and print again, and again and again up to a set number of times.

So far i found a VBA code and modified it to work as below,

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want?", Type:=1)

For CopieNumber = Range("A1") + 1 To CopiesCount
With ActiveSheet
.Range("A1").Value = CopieNumber

.PrintOut
End With
Next CopieNumber
End Sub

However this only works if the initial number is 0 or 1. if i put any number such as 2 or 1234567 it doesnt print or increment.Theres only going to be a maximum of 7 digits needed but not sure if it has to always start at 1 or if its to be higher.
Also is it possible for tehmacro to set the font size and style, posistioning and layout (such as landcape)?

I know its asking a lot, but i know i'm in the right place.

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Hi Twizzle,
Found this site, and the code is similar to what you have already.

Does it solve the problem?

NB

 

Thats the site i found teh original code at and i've modified it as that didnt quite work the way i wanted. I've now added
ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Name = "Arial"
ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Size = "100"

to the start of my Macro to set the font style and size which works. Now for positioning and sorting the count issue.
What i have found is that if my starting number is 10 and i put in count 15 it prints numbers 11 to 15. It look sliek it only goes as far as teh count number not actuall adds +1 to any number that many times.

 

Hi Twizzle,

A couple of the changes you made to the original code may be causing some issues, try this:

Code:

Sub PrintCopies_ActiveSheet()

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Name = "Arial"
ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Size = "100"

Dim CopiesCount As Long
Dim copienumber As Long

PreviousState = Range("A1").Value

CopiesCount = Application.InputBox("How many Copies do you want?", Type:=1)

For copienumber = 1 To CopiesCount
With ActiveSheet


.Range("A1").Value = PreviousState + copienumber


.PrintOut
End With
Next copienumber
Range("A1").Value = PreviousState + CopiesCount
End Sub

You may need to reset the "A1" value each time or alter that part of the code.

 

Thanks BB thats close enough for what i need that i can live with it.

 

Is your starting number always going to start at a given number or will they run consecutively? You can always add an inputbox for a start number in needed.

As a side note, while your still testing the script, you might replace the .PrintOut with ActiveWindow.SelectedSheets.PrintPreview. It sends the printout into print preview. Saves on paper.

I haven't worked with macros for quite some time, and then it was with Lotus 123, hope I don't throw any wrenches in the spokes here.