Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Consecutive numbering (2002 v10)

    Okay, this should be easy but I'm not too savvy with macros. What I'm trying to do is create a macro that will number pallet sheets consecutively up to a specific number. There are two pallet sheets per page, one above the other. I need to have them print out with the odd numbers on top and even numbers on the bottom so when I cut them in half I can just stack the top sheets on top of the bottom ones. I've attached a copy of the form I use. Thanks in advance
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consecutive numbering (2002 v10)

    What exactly do you want?
    Would you like to print the single page in the sheet as many times as needed, each time increasing the numbers?
    Or would you like to duplicate the single page within the sheet as many times as needed, while populating the numbers, so that it can be printed in one go?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Consecutive numbering (2002 v10)

    I would like to duplicate the sheet in one go, each time increasing the numbers from 1 to 40. In other words, the top of the first page would have the number "1" after "Pallet #" and the bottom part of the first page would have the number "21" after "Pallet #". The top of the second page would have number "2" after "Pallet #" and the bottom part of the second page would have the number "22" after "Pallet #", and so on. Sorry I didn't make this clear; hope you are able to understand what I need. Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consecutive numbering (2002 v10)

    You'll have to remove the print range that you have set, otherwise you'll only be able to print one page. I{ had to adjust the labels slightly to make 2 fit on a page.

    The attached version of the workbook contains the following macro.

    Sub CreateLabels()
    Const intHeight = 26 ' Height of the page in rows
    Const intFirst = 6 ' Row of first pallet number
    Const intSecond = 23 ' Row of second pallet number

    Dim i As Integer
    Dim n As Integer

    n = Application.InputBox(Prompt:="How many labels do you want to create?", Type:=1)
    If n < 1 Then
    MsgBox "Number must be at least 1.", vbExclamation
    Exit Sub
    End If

    If n Mod 2 = 1 Then
    n = n + 1
    MsgBox "Number must be even, it has been increased to " & n, vbInformation
    End If

    Cells(intFirst, 5) = 1
    Cells(intFirst, 5).Font.Size = 32
    Cells(intSecond, 5) = n / 2 + 1
    Cells(intSecond, 5).Font.Size = 32

    For i = 2 To n / 2
    Range("1:" & intHeight).Copy Destination:=Cells(intHeight * (i - 1) + 1, 1)
    Cells(intHeight * (i - 1) + intFirst, 5) = i
    Cells(intHeight * (i - 1) + intSecond, 5) = n / 2 + i
    Next i
    End Sub
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Consecutive numbering (2002 v10)

    Works great! It certainly wasn't as straightforward as I thought it would be. You guys are great! Never been disappointed when I ask for help. You make me look good, but I always tell everyone I'd be dead in the water without Woody's Lounge. Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •