1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel macro for numbering

Discussion in 'Microsoft Office Specialist (MOS)' started by twizzle, Jun 17, 2010.

  1. twizzle

    twizzle Gigabyte Poster

    1,838
    33
    104
    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.
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,
  2. Notes_Bloke

    Notes_Bloke Terabyte Poster

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

    Does it solve the problem?

    NB
     
    Certifications: 70-210, 70-215, A+,N+, Security+
    WIP: MCSA
  3. twizzle

    twizzle Gigabyte Poster

    1,838
    33
    104
    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.
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,
  4. BrotherBill

    BrotherBill Byte Poster

    228
    24
    15
    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.
     
    Last edited: Jun 17, 2010
  5. twizzle

    twizzle Gigabyte Poster

    1,838
    33
    104
    Thanks BB thats close enough for what i need that i can live with it.
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,
  6. BrotherBill

    BrotherBill Byte Poster

    228
    24
    15
    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.
     

Share This Page

Loading...