Print multiple reports from list validation
 

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

Print multiple reports from list validation
 

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



"Zilla" wrote in message
...
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

Print multiple reports from list validation
 

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" wrote:

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



"Zilla" wrote in message
...
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

Print multiple reports from list validation
 

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



"Zilla" wrote in message
...
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" wrote:

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



"Zilla" wrote in message
...
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