Recording a Macro with a Validation List

[zburns]

I have to prepare 70+ tables from a data set. In order to take the same information from the data set, I have created a validation list dropdown (in cell A2). The next step (that I need help with) is recording a macro that prints the first table, then selects the next item on the list and prints that table, continuing until all 70 table are printed.

I tried to record the macro, but the macro won't select the following item on the list before printing. Does anyone know how to edit the macro to do this?

Thank you for your help!

 

[Andrew Poulsom]

Can't you just loop around the validation list, setting A2 to each item and printing the data?

 

[zburns]

I think that is what I tried, but when I ran the macro, the same item on the list printed over again, rather than the next item. Also, when I recorded the macro, I printed as far as the third item on the list; I need the macro to continue running until it has cycled through all of the items on the list.

 

[zburns]

This is the code for the macro I recorded. I don't know enough about coding macros to be able to change it however... any more help is much appreciated.

Sub TablePrint()
'
' TablePrint Macro
' Switch to next table, then print.
'

'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

 

[Andrew Poulsom]

What's the Source of the data validation list?

 

[zburns]

The list is on the same tab as the table (the tab is titled "Tables") in cells AF2:AF78.

 

[Andrew Poulsom]

Try:

Sub TablePrint()
    Dim Cell As Range
    For Each Cell In Range("AF2:AF78")
        Range("A2").Value = Cell.Value
        ActiveSheet.PrintOut
    Next Cell
End Sub

 

[zburns]

That worked!! Thank you so much! A huge help!