Close

Results 1 to 6 of 6
  1. #1
    DF VIP Member
    satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,662
    Thanks
    157
    Thanked:        564
    Karma Level
    469

    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?C Class Items Kit locator Mk III.zip
    Just use enough water to cover your vegetables,the same goes for when you're having a bath....

  2. #2
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    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 VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    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 VIP Member
    satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,662
    Thanks
    157
    Thanked:        564
    Karma Level
    469

    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 VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    369

    Default Re: Excel printing macro help please

    How did you get on Satzzz?

  6. #6
    DF VIP Member
    satzzz's Avatar
    Join Date
    Oct 2000
    Location
    Here
    Posts
    2,662
    Thanks
    157
    Thanked:        564
    Karma Level
    469

    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)  


Similar Threads

  1. [HELP] Excel Macro Help - Run macro using active sheet
    By Karoline in forum Programming
    Replies: 2
    Last Post: 11th December 2014, 04:36 PM
  2. [HELP] Desperate kids excel coursework need help with macro
    By Teajunkie in forum Programming
    Replies: 8
    Last Post: 22nd October 2014, 12:25 PM
  3. Silly Macro... or perhaps not so Macro?
    By dpSparhawk in forum Your Photos & Artwork
    Replies: 2
    Last Post: 4th August 2013, 11:29 PM
  4. Excel Macro basics
    By Gazzr in forum Programming
    Replies: 11
    Last Post: 3rd November 2011, 10:06 PM
  5. Excel Macro Prob
    By Hadfield82 in forum Programming
    Replies: 2
    Last Post: 27th January 2007, 08:33 PM

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
  •