Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
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 Ross wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
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 "Ross" skrev i meddelelsen ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
For the OP
See also this code from John Green for faster code http://www.mcgimpsey.com/excel/udfs/pagesetup.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... 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 Ross wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
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 "Dave Peterson" wrote: 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 Ross wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Formating using VBA
It makes perfectly good sense. But doesn't my suggestion may sense, too?
Ross wrote: 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 "Dave Peterson" wrote: 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 Ross wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying print formating | Excel Discussion (Misc queries) | |||
formating zip codes with leading zero to print zero | Excel Discussion (Misc queries) | |||
Print Formating | Excel Discussion (Misc queries) | |||
Formating Print Margins | Excel Programming | |||
Flat File formating with Print# Statement | Excel Programming |