Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can all the sheets in one workbook all the same?
I have a workbook with 20+ worksheets. I don't want to waste a lot of time
formating each page for margins and landscape. Is there a way to set just this one workbook to format the same from the first page? |
#2
|
|||
|
|||
Can all the sheets in one workbook all the same?
Select them all with the sheet tabs (select the first, then click-select the
last, and you will see Group in the workbook title bar), and then apply your format. -- HTH RP (remove nothere from the email address if mailing direct) "camalex03" wrote in message ... I have a workbook with 20+ worksheets. I don't want to waste a lot of time formating each page for margins and landscape. Is there a way to set just this one workbook to format the same from the first page? |
#3
|
|||
|
|||
Can all the sheets in one workbook all the same?
I guess I am not as experienced as I thought. I don't follow you. Could you
explain a different way maybe? I am sorry. "Bob Phillips" wrote: Select them all with the sheet tabs (select the first, then click-select the last, and you will see Group in the workbook title bar), and then apply your format. -- HTH RP (remove nothere from the email address if mailing direct) "camalex03" wrote in message ... I have a workbook with 20+ worksheets. I don't want to waste a lot of time formating each page for margins and landscape. Is there a way to set just this one workbook to format the same from the first page? |
#4
|
|||
|
|||
Can all the sheets in one workbook all the same?
I've written some code that reads the page setup of the current
worksheet and applies those settings to all the tabs in the workbook. If you try this code, run it on a Backup<< file first to make sure it doesn't destroy your data. The caveat is: I have tested this code on a few but not all possible combinations of print setups. It worked fine for the combinations I tested but may choke on your particular set of combinations. If it does choke, the code will generate an error and stop; I would appreciate it if you'd contact me with problems that you encounter so I can update and improve the code. Sub CopyPrintFormats() 'declare booleans Dim dBlackAndWhite As Boolean, dCenterHorizontally As Boolean, _ dCenterVertically As Boolean, dDraft As Boolean, dPrintGridlines As Boolean, _ dPrintHeadings As Boolean, MasterZoom As Boolean 'declare longs Dim dZoom As Long, dFitToPagesWide As Long, dFitToPagesTall As Long 'declare strings Dim dCenterFooter As String, dCenterHeader As String, dLeftFooter As String, _ dLeftHeader As String, dPrintArea As String, dPrintTitleColumns As String, _ dPrintTitleRows As String, dRightFooter As String, dRightHeader As String 'declare variants Dim dBottomMargin As Variant, dFirstPageNumber As Variant, dFooterMargin As Variant, _ dHeaderMargin As Variant, dLeftMargin As Variant, dOrder As Variant, _ dOrientation As Variant, dPaperSize As Variant, dPrintComments As Variant, _ dPrintErrors As Variant, dRightMargin As Variant, dTopMargin As Variant, dPrintQuality As Variant With ActiveSheet.PageSetup dBlackAndWhite = .BlackAndWhite dCenterHorizontally = .CenterHorizontally dCenterVertically = .CenterVertically dDraft = .Draft dPrintGridlines = .PrintGridlines dPrintHeadings = .PrintHeadings dPrintQuality = .PrintQuality dFitToPagesTall = .FitToPagesTall dFitToPagesWide = .FitToPagesWide If .Zoom Then dZoom = .Zoom MasterZoom = True End If dCenterFooter = .CenterFooter dCenterHeader = .CenterHeader dLeftFooter = .LeftFooter dLeftHeader = .LeftHeader dPrintArea = .PrintArea dPrintTitleColumns = .PrintTitleColumns dPrintTitleRows = .PrintTitleRows dRightFooter = .RightFooter dRightHeader = .RightHeader dBottomMargin = .BottomMargin dFirstPageNumber = .FirstPageNumber dFooterMargin = .FooterMargin dHeaderMargin = .HeaderMargin dLeftMargin = .LeftMargin dOrder = .Order dOrientation = .Orientation dPaperSize = .PaperSize dPrintComments = .PrintComments dPrintErrors = .PrintErrors dRightMargin = .RightMargin dTopMargin = .TopMargin End With Dim SName As Variant For Each SName In Sheets Sheets(SName.Name).Select With ActiveSheet.PageSetup .BlackAndWhite = dBlackAndWhite .CenterHorizontally = dCenterHorizontally .CenterVertically = dCenterVertically .Draft = dDraft .PrintGridlines = dPrintGridlines .PrintHeadings = dPrintHeadings .PrintQuality = dPrintQuality .FitToPagesTall = dFitToPagesTall .FitToPagesWide = dFitToPagesWide If MasterZoom Then .Zoom = dZoom Else .Zoom = False End If .CenterFooter = dCenterFooter .CenterHeader = dCenterHeader .LeftFooter = dLeftFooter .LeftHeader = dLeftHeader .PrintArea = dPrintArea .PrintTitleColumns = dPrintTitleColumns .PrintTitleRows = dPrintTitleRows .RightFooter = dRightFooter .RightHeader = dRightHeader .BottomMargin = dBottomMargin .FirstPageNumber = dFirstPageNumber .FooterMargin = dFooterMargin .HeaderMargin = dHeaderMargin .LeftMargin = dLeftMargin .Order = dOrder .Orientation = dOrientation .PaperSize = dPaperSize .PrintComments = dPrintComments .PrintErrors = dPrintErrors .RightMargin = dRightMargin .TopMargin = dTopMargin End With Next SName End Sub |
#5
|
|||
|
|||
Can all the sheets in one workbook all the same?
Forgot to add: please contact me, cyclezen atsign yahoo dot com, with
comments. |
#6
|
|||
|
|||
Can all the sheets in one workbook all the same?
Dave,
WOW! I don't know how or where to start with that? Can you guide me? "Dave O" wrote: I've written some code that reads the page setup of the current worksheet and applies those settings to all the tabs in the workbook. If you try this code, run it on a Backup<< file first to make sure it doesn't destroy your data. The caveat is: I have tested this code on a few but not all possible combinations of print setups. It worked fine for the combinations I tested but may choke on your particular set of combinations. If it does choke, the code will generate an error and stop; I would appreciate it if you'd contact me with problems that you encounter so I can update and improve the code. Sub CopyPrintFormats() 'declare booleans Dim dBlackAndWhite As Boolean, dCenterHorizontally As Boolean, _ dCenterVertically As Boolean, dDraft As Boolean, dPrintGridlines As Boolean, _ dPrintHeadings As Boolean, MasterZoom As Boolean 'declare longs Dim dZoom As Long, dFitToPagesWide As Long, dFitToPagesTall As Long 'declare strings Dim dCenterFooter As String, dCenterHeader As String, dLeftFooter As String, _ dLeftHeader As String, dPrintArea As String, dPrintTitleColumns As String, _ dPrintTitleRows As String, dRightFooter As String, dRightHeader As String 'declare variants Dim dBottomMargin As Variant, dFirstPageNumber As Variant, dFooterMargin As Variant, _ dHeaderMargin As Variant, dLeftMargin As Variant, dOrder As Variant, _ dOrientation As Variant, dPaperSize As Variant, dPrintComments As Variant, _ dPrintErrors As Variant, dRightMargin As Variant, dTopMargin As Variant, dPrintQuality As Variant With ActiveSheet.PageSetup dBlackAndWhite = .BlackAndWhite dCenterHorizontally = .CenterHorizontally dCenterVertically = .CenterVertically dDraft = .Draft dPrintGridlines = .PrintGridlines dPrintHeadings = .PrintHeadings dPrintQuality = .PrintQuality dFitToPagesTall = .FitToPagesTall dFitToPagesWide = .FitToPagesWide If .Zoom Then dZoom = .Zoom MasterZoom = True End If dCenterFooter = .CenterFooter dCenterHeader = .CenterHeader dLeftFooter = .LeftFooter dLeftHeader = .LeftHeader dPrintArea = .PrintArea dPrintTitleColumns = .PrintTitleColumns dPrintTitleRows = .PrintTitleRows dRightFooter = .RightFooter dRightHeader = .RightHeader dBottomMargin = .BottomMargin dFirstPageNumber = .FirstPageNumber dFooterMargin = .FooterMargin dHeaderMargin = .HeaderMargin dLeftMargin = .LeftMargin dOrder = .Order dOrientation = .Orientation dPaperSize = .PaperSize dPrintComments = .PrintComments dPrintErrors = .PrintErrors dRightMargin = .RightMargin dTopMargin = .TopMargin End With Dim SName As Variant For Each SName In Sheets Sheets(SName.Name).Select With ActiveSheet.PageSetup .BlackAndWhite = dBlackAndWhite .CenterHorizontally = dCenterHorizontally .CenterVertically = dCenterVertically .Draft = dDraft .PrintGridlines = dPrintGridlines .PrintHeadings = dPrintHeadings .PrintQuality = dPrintQuality .FitToPagesTall = dFitToPagesTall .FitToPagesWide = dFitToPagesWide If MasterZoom Then .Zoom = dZoom Else .Zoom = False End If .CenterFooter = dCenterFooter .CenterHeader = dCenterHeader .LeftFooter = dLeftFooter .LeftHeader = dLeftHeader .PrintArea = dPrintArea .PrintTitleColumns = dPrintTitleColumns .PrintTitleRows = dPrintTitleRows .RightFooter = dRightFooter .RightHeader = dRightHeader .BottomMargin = dBottomMargin .FirstPageNumber = dFirstPageNumber .FooterMargin = dFooterMargin .HeaderMargin = dHeaderMargin .LeftMargin = dLeftMargin .Order = dOrder .Orientation = dOrientation .PaperSize = dPaperSize .PrintComments = dPrintComments .PrintErrors = dPrintErrors .RightMargin = dRightMargin .TopMargin = dTopMargin End With Next SName End Sub |
#7
|
|||
|
|||
Can all the sheets in one workbook all the same?
Sure!
1. Make a copy of the file you want to work on. Close any other open files. 2. Open the backup file and click View Toolbars, then check Visual Basic. This will cause the Visual Basic toolbar to appear on screen. Drag your cursor over the various icons: the name of each icon will appear in a little box; click the one that says Visual Basic Editor. The window should have two panes: a VBA Project pane on the left and a white open space on the right. 3. In the project pane, the words VBAProject (Your file name here) should appear, along with perhaps one or two other "overhead" files. Click the + sign to expand it, if necessary. Right click VBAProject(Your file name), select Insert, select Module. 4. Double click the Module you just inserted; the right-hand side of the screen will flicker, opening a new editor space for you. 5. Copy the program I posted, from Sub CopyPrintFormats() to End Sub inclusive into your clipboard. Paste it into the Visual Basic editor space you created in step 4. Save the file. 6. Click back to your spreadsheet file, and display the tab you've set up with your desired print parameters. Click Tools Macro Macros; highlight the CopyPrintFormats macro, and select Run. The program copies the print parameters from this tab into all the other tabs in the file. Please let me know how it works! |
#8
|
|||
|
|||
Can all the sheets in one workbook all the same?
Well, first click the first of the sheet tabs (those tabs with the sheet
names at the foot of the spreadsheet) Then Shift-click the last of those tabs You will now see 'Group' in the workbook title bar, the blue bar at the top of the workbook/spreadsheet The sheets are now grouped, so anything that you do on the one that you see, including formatting, will be replicated on the others. -- HTH RP (remove nothere from the email address if mailing direct) "camalex03" wrote in message ... I guess I am not as experienced as I thought. I don't follow you. Could you explain a different way maybe? I am sorry. "Bob Phillips" wrote: Select them all with the sheet tabs (select the first, then click-select the last, and you will see Group in the workbook title bar), and then apply your format. -- HTH RP (remove nothere from the email address if mailing direct) "camalex03" wrote in message ... I have a workbook with 20+ worksheets. I don't want to waste a lot of time formating each page for margins and landscape. Is there a way to set just this one workbook to format the same from the first page? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 sheets in workbook, but visible only one? | Excel Discussion (Misc queries) | |||
Copy formatted in several sheets of a workbook | Excel Worksheet Functions | |||
Macros for Protect/Unprotect all sheets in a workbook | Excel Discussion (Misc queries) | |||
Sending sheets in workbook | Excel Discussion (Misc queries) | |||
Automatically copy selective sheets from one workbook to another | Excel Discussion (Misc queries) |