Creating print areas automatically using VBA

[Guest]

I have an Excel file in which a worksheet contains 365 identical tables
except for the date.

I wish to make all these tables individual print areas i.e end up with 365
print areas.

I wish to do this automatically using VBA code.

My only attempt to was use the following code on each table using a loop.

Sub PrintArea()

Application.ScreenUpdating = False

Worksheets("Sheet1").Activate

For t = 1 To 13141 Step 36

Range("A1")(t).Activate

ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

Next t

Application.ScreenUpdating = True

End Sub

I used Step Into to view the operation of the code one line at a time.

Unfortunately as soon as the second table became a print area the first
table print area was deselected
As soon as the third table became a print area the second table print area
was deselected and so on.

The result of running the code was simply to make the last table a print
area.

Any suggestions please ?

I am quite willing to upload a copy of the file if this is at all possible.

 

[Guest]

There is only one print area per sheet. The behavior you describe is exactly
what I would expect.

The question is, what are you trying to accomplish with this?

Are you trying to do a print and have each table print out on a separate
sheet?

Perhaps a clearer explanation of what you hope to achieve will result in a
suggestion on how to approach the problem.

I wish to make all these tables individual print areas i.e end up with 365
print areas.

so you see that is not possible - but what funtionality are you trying to
achieve?

 

[Guest]

Thanks Tom for your rapid reply and the info given. I shall try this out
shortly.
However you say you can only have one print area per worksheet.

I have created a file with a worksheet having 8 print areas.

These are created by selecting the 1st area , then while holding down Ctrl
selecting all the remaining areas.

I convert all of these to print areas by clicking on File -----> Print Area
------> Set Print area .

The purpose of these multi print areas is to print out some or all of these
areas without having to create page breaks . Print preview will display all
these print areas in turn.

Do you mean I cannot more than one print area using VBA code ?

Regards

 

[Penny Whittaker]

The solutions here is to assign range names to each table. You can then select a specific range and print the select area. Why do you need a macro to do this?