Close

Results 1 to 6 of 6
  1. #1
    DF Jedi satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,645
    Thanks
    151
    Thanked:        553
    Karma Level
    415

    Default Excel printing macro help please

    Gent's I'm after help with the attached spread sheet. What I'm after is to auto print certain info onto labels. In the attached example when the sheet has been filled in on each label I would require the Engine, Build Pack and Pass To info, on every label.
    Then the part number, description, QTY required and bin location (these would change on every label as you can see from the attached)
    I would like it so on pressing print it would print all labels one after each other without having to select each lot of data then print.
    Any ideas?[Only registered and activated users can see links. ]
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Excel printing macro help please

    Wheres the label layout you want?

    As you could then loop thru the rows populating and printing this each time?

    At the moment you are just printing the list and several blank pages?

    Thanks to akimba

    satzzz (6th March 2018) 


  3. #3
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Excel printing macro help please

    Heres an example I have knocked up for Sheet1
    It populates Sheet1 C rows meaning you can title the B rows i.e. B2 = "Engine"

    Sub Macro1()
    Dim i As Integer
    Dim iMax As Integer
    i = 9 'First row of list
    iMax = LastRow("A", 9, "Pick List")

    Worksheets("Sheet1").Activate

    Worksheets("Sheet1").Range("C2").Value = Worksheets("Pick List").Range("C1").Value
    Worksheets("Sheet1").Range("C3").Value = Worksheets("Pick List").Range("C2").Value
    Worksheets("Sheet1").Range("C4").Value = Worksheets("Pick List").Range("C3").Value

    Do While i < iMax
    Worksheets("Sheet1").Range("C6").Value = Worksheets("Pick List").Range("A" & CStr(i)).Value
    Worksheets("Sheet1").Range("C7").Value = Worksheets("Pick List").Range("B" & CStr(i)).Value
    Worksheets("Sheet1").Range("C8").Value = Worksheets("Pick List").Range("C" & CStr(i)).Value
    Worksheets("Sheet1").Range("C9").Value = Worksheets("Pick List").Range("e" & CStr(i)).Value
    ActiveSheet.PageSetup.PrintArea = "$A$1:$E$10"
    ActiveSheet.PrintPreview
    i = i + 1
    Loop
    MsgBox "All Printed"
    End Sub

    Function LastRow(iColumn, iStart, sWorksheet) As Integer
    Dim lrn
    lrn = iStart
    Do Until Worksheets(sWorksheet).Range(iColumn & Trim(Str(lrn))) = ""
    lrn = lrn + 1
    Loop
    LastRow = lrn - 1
    End Function

    Thanks to akimba

    satzzz (6th March 2018) 


  4. #4
    DF Jedi satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,645
    Thanks
    151
    Thanked:        553
    Karma Level
    415

    Default Re: Excel printing macro help please

    Cheers pal. I shall take a look when at work tomorrow morning

    Sent from my Philips Cellnet using a 12c508
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

  5. #5
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Excel printing macro help please

    How did you get on Satzzz?

  6. #6
    DF Jedi satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,645
    Thanks
    151
    Thanked:        553
    Karma Level
    415

    Default Re: Excel printing macro help please

    Quote Originally Posted by akimba View Post
    How did you get on Satzzz?
    With a few tweaks it's running fine pal. Thanks for your help

    Sent from my Philips Cellnet using a 12c508
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

    Thanks to satzzz

    akimba (12th March 2018) 


Social Networking Bookmarks

Posting Permissions

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