How automate printing a spreadsheet multiple times with different data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mvent - 13 Nov 2003 00:31 GMT

I have a workbook containing two worksheets. One worksheet is built like a table, the other, like a form. I would like to print the form for each line of the table (200+). Can this be automated? Maybe a macro that changes the cell reference for each print? Thanks

Reply to this Message

Paul Cundle - 13 Nov 2003 23:53 GMT

It can be done, and indeed I have done something very similar for my
employer using lookup tables, but I suggest asking again in
news:microsoft.public.excel.programming
as the solutions are likely to be tidier (also because this group is quiet
and if you're relying on me then it could take some time under current
circumstances).

Paul C,

Signature

> I have a workbook containing two worksheets. One worksheet is built
> like a table, the other, like a form. I would like to print the form
> for each line of the table (200+). Can this be automated? Maybe a
> macro that changes the cell reference for each print? Thanks

Reply to this Message

Mike - 20 Nov 2003 22:42 GMT

You'll need to provide a little more information as well if you want a
fairly complete solution - some of the people on mpep are very quick if you
give enough info.

Anyways, as a basic generic example, you would want to do something like

Sub test()
Dim a As Integer
For a = 1 To 200
with ThisWorkbook.Worksheets("Sheet1")
.Range("A1").Formula = "=Sheet2!G" & a
.PrintOut
end with
Next a

End Sub

where "Sheet1" would be the name of your 'form' worksheet, and "Sheet2"
would be the name of your table worksheet, and Sheet1 A1 is the cell on the
form that points to the data, and Sheet2 G1 to G200 would be where all the
pertinent data is.

hope that helps.

Signature

____________________________________________________________________________
________________
Please reply to newsgroup so everyone can benefit.
Email address is not valid (see sparkingwire.com)
____________________________________________________________________________
________________

> It can be done, and indeed I have done something very similar for my
> employer using lookup tables, but I suggest asking again in
[quoted text clipped - 8 lines]
> > for each line of the table (200+). Can this be automated? Maybe a
> > macro that changes the cell reference for each print? Thanks

Reply to this Message

Rate this thread: