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!!

 

[Conan257]

Not the answer you're after, but wouldn't access be a better application for this? Its designed to look at different databases/tables etc...

 

[DireEmblem]

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!

 

[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!

 

[DireEmblem]

Glad i could help...

But the below was more of what I was meaning:-

Sub PrintOut()
'
    Dim row As Integer
    Dim copy As String

    row = 5
    
    Worksheets("Data").Select
    copy = Range("D" & row).Value
    
    While copy <> ""
        
        Worksheets("Information").Select
        Worksheets("Information").Range("D5").Value = copy
        
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        
        Worksheets("Data").Select
        row = row + 1
        copy = Range("D" & row).Value
    Wend

End Sub

So it starts reading from D&Row (D5) from the data tab, if the cells not empty, it will go into the loop, and copy the ID over to your information tab, and then print. It will then increment the row by 1, and check D6, and so on, so on...