Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Print Formating using VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ross - 23 May 2008 19:51 GMT
Please help,

I am formatting each page to print using vba and it is very cool but slow
with 75 tabs / pages to format.

I can select all tabs (with vba) and manually format all selected tabs but
my "With Reference" only allows 1 tab / Page.

How can I do all the pages (.PageSetup) with one pass?

My Code:
      With ActiveSheet.PageSetup
       .LeftHeader = ""
       .CenterHeader = "&A"
.
.
.
.
.etc

What should I change "With ActiveSheet.PageSetup" to so that it can deal
with ALL SELECTED SHEETS instead of doing just one sheet even though all are
selected?

Many Thanks

Ross
Dave Peterson - 23 May 2008 20:06 GMT
Dim wks as worksheet
for each wks in activewindow.selected sheets
  wks.activate
  your code here
next wks

or

Dim wks as worksheet
for each wks in activeworkbook.worksheets 'to get all the sheets
  wks.activate
  your code here
next wks

> Please help,
>
[quoted text clipped - 23 lines]
>
> Ross

Signature

Dave Peterson

Ron de Bruin - 23 May 2008 20:40 GMT
For the OP

See also this code from John Green for faster code
http://www.mcgimpsey.com/excel/udfs/pagesetup.html

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Dim wks as worksheet
> for each wks in activewindow.selected sheets
[quoted text clipped - 37 lines]
>>
>> Ross
Ross - 23 May 2008 20:45 GMT
Dave,  

Thank you for your response:
To clerify, I have code that runs prior to the code pasted into this "Blog"
that correctly selects all of the sheets/tabs prior to running the  
formatting code:

This runs to select the sheets:
'*********************
Sub Select_All_Sheets()
'*********************
Dim Sh As Worksheet
   For Each Sh In ActiveWorkbook.Worksheets
           Sh.Select False
   Next Sh
End Sub

The problem is that when the code previously pasted runs, it "de-selects"
all of the previously selected sheets and formats only one active sheet and
not the 75 previously selected sheets.

That is why I think that "With ActiveSheet.PageSetup" only allows one sheet
at a time.  AS previously described, I can stop the code before "With
ActiveSheet.PageSetup" and manually adjust all 75 sheets at once (say change
from portrait to landscape). I need code that will allow "With
ActiveSheet(S).PageSetup" (plural) instead of singular.

Does this make sense?

Thanks again!

Ross

> Dim wks as worksheet
> for each wks in activewindow.selected sheets
[quoted text clipped - 37 lines]
> >
> > Ross
Dave Peterson - 23 May 2008 21:54 GMT
It makes perfectly good sense.  But doesn't my suggestion may sense, too?

> Dave,
>
[quoted text clipped - 74 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Per Jessen - 23 May 2008 20:16 GMT
Hi Ross

You can not edit headers for all sheets at once. Try this instead:

For Each sh In ThisWorkbook.Sheets
   With sh.PageSetup
       .LeftHeader = ""
       .CenterHeader = "&A"
   End With
Next

Regards,
Per

> Please help,
>
[quoted text clipped - 24 lines]
>
> Ross
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.