Thread: 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

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" <Zilla@discussions.microsoft.com> wrote in message
news:DEAAA327-B1BC-4BCE-86F5-37799D83A108@microsoft.com...
> 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
>
>

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" <Zilla@discussions.microsoft.com> wrote in message
> news:DEAAA327-B1BC-4BCE-86F5-37799D83A108@microsoft.com...
> > 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
> >
> >
>
>
>

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" <Zilla@discussions.microsoft.com> wrote in message
news:992D7E7A-008A-4AB3-8A13-107A65E464F0@microsoft.com...
> 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" <Zilla@discussions.microsoft.com> wrote in message
>> news:DEAAA327-B1BC-4BCE-86F5-37799D83A108@microsoft.com...
>> > 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
>> >
>> >
>>
>>
>>