Print multiple reports from list validation

[Guest]

Hi,

I am new to Excel vba - I have some experience in Access vba however.Can
anyone help me implement a solution to my problem I picked up elsewhere on
these forums?

I have a single a4 report with various charts, the content of the report
comes from a drop down list pointing to a list of values elsewhere on the
same worksheet.

I want a button that will work throught the entire list and print the report
for each one. I have found this solution elsewhere (thanks to Tom Ogilvy):

for each cell in Range("NamedRangeName")
Range("A1").Value = cell.Value
' print sheet
Next

Which apparently does what I need, but I really don't know how to implement
this - can anyone guide me? I'm at the copying and pasting stage right now,
which obviously does'nt work... I need to know in a really exact way how to
get this going.

This is perhaps too basic a question for this board, but I just need a quick
solution... help!

Thanks in advance

 

[Chip Pearson]

You need to put the code in a Sub procedure, and that Sub
procedure should be in a standard code module, not the
ThisWorkbook code module or a Sheet code module. Open the VBA
Editor (ALT+F11), open the Project Explorer (CTRL+R) and locate
your project in the tree view control. Go to the Insert menu and
choose Module. In that module, paste the following code:


Sub AAA()
Dim Cell As Range
For Each Cell In Range("NamedRangeName")
Range("A1").Value = Cell.Value
' print sheet
Next Cell

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks Chip,

Still a bit stuck I'm afraid:

It now looks like this:

Sub AAA()
Dim Cell As Range
For Each Cell In Range("t2:t10")
Range("d1").Value = Cell.Value
Print Sheet
Next Cell

End Sub

but the code breaks on the print command saying: "Compile error, method not
valid without suitable object"

Any suggestions?
Thanks again

 

[Chip Pearson]

Zilla,

The "Print" command is actually used for writing to files, not
printing. Use code like

ActiveSheet.PrintOut

to print out a worksheet. Get rid of

Print Sheet