Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating print areas automatically using VBA
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. -- FLYNNE |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating print areas automatically using VBA
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? -- Regards, Tom Ogilvy "FLYNNE" wrote: 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. -- FLYNNE |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating print areas automatically using VBA
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 -- FLYNNE "Tom Ogilvy" wrote: 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? -- Regards, Tom Ogilvy "FLYNNE" wrote: 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. -- FLYNNE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I print multiple print areas on one page | Excel Discussion (Misc queries) | |||
Can I adjust print scaling for multiple print areas on the same sh | Excel Discussion (Misc queries) | |||
Need to print a workbook but worksheets have diff print areas | Excel Discussion (Misc queries) | |||
printing multiple print areas with a print dialog box | Excel Programming | |||
Multiple print areas, one worksheet -need to print to PDF! | Excel Programming |