Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto lookup print macro (Excel 2000)

    I have a simple Excel file (attached) in which I want to have a macro created which will read sequentially each cell in column J (item number) and copy it over to B2. Once this item number is copied to B2, its production cost will be looked up (from a named range COST) and displayed in D2. This macro will then print out the range A1:F5. This process will be repeated automatically by the macro until no more item and cost data in columns J & K.

    Can someone please help me out?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto lookup print macro (Excel 2000)

    Assuming the printarea has been set, this macr will do the trick:

    Option Explicit

    Sub PrintThem()
    Dim lCount As Long
    Dim oSheet As Worksheet
    lCount = 0
    Set oSheet = ThisWorkbook.Worksheets("Sheet1")
    Do While oSheet.Range("J4").Offset(lCount, 0) <> ""
    oSheet.Range("B2").Value = oSheet.Range("J4").Offset(lCount, 0)
    oSheet.PrintOut
    lCount = lCount + 1
    Loop
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto lookup print macro (Excel 2000)

    Thanks and it works!

    However, I have a named range called PrintArea. How can I set it so that only the range, instead of the entire worksheet, will be printed?

    thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: auto lookup print macro (Excel 2000)

    Add this line to your code to set the print area

    <pre> ActiveSheet.PageSetup.PrintArea = "PrintArea"</pre>


    Or define the named range as Print_Area (instead of PrintArea) which is what excel names a defined print area

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto lookup print macro (Excel 2000)

    It is not wise to use the propriatry name Print_Area, since it will get overwritten as soon as you set another printarea for that sheet.

    In fact this name behaves strange if you try to set it. Normally a name is global by default. If however you try to define the name Print_Area, Excel will default to a local name! It seems impossible to define a global name called Print_Area.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto lookup print macro (Excel 2000)

    Thanks it works great!

Posting Permissions

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