Excel Macro question - printing a sheet over and over with different values?

[Carzy]

Hi,

Been using Excel for a while now, but only just started using macros. I have a large table of data in one sheet on Excel, and then I have another sheet whereby you put in the name of an object in the table, and it will pull via vlookups, some specific information off the table about that object and display it for you. Now I have loads of objects in the table, and instead of manually going in and copying each name from the table of data into the other sheet, I want it to automatically pull every object in say....column D, put it individually into my top sheet, and then print off the data that the vlookups provide.

Like so:

So I want to individually pull everything that has an entry for "Description", put it into the highlighted cell each time, have my vlookups pull all the data, and then print that sheet off.

I can get it to follow a macro where I have to click each description I want the first time around and then it will copy me if I ever want to do it again, but that's not very helpful when I have hundreds of descriptions. How do I get it to look at a range of cells but take each one in turn?

Thanks for any help!!

 

[Carzy]

cant be bothered writing this for you atm, but record yourself copying the details over from the first row, and then printing the page after the vlookup.

Put this in a while loop, and hey presto done...

(very rough pseudo code)

Start row = 5
While d&Row value <> "" (null)
     Copy your the id from Row over
     Print the sheet
     row=row+1
Wend

Best way to learn is to do it yourself from scratch!

Perfect - that "While" command is what I needed....with my rubbish coding skills I came up with:

Sub Again()
'
' Again Macro
' Macro recorded 12/01/2009 by Carzy
'

'
    Do
    Sheets("Sheet2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Sheet2").Select
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
    
End Sub

Thanks very much!