Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
camalex03
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
camalex03
 
Posts: n/a
Default 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   Report Post  
Dave O
 
Posts: n/a
Default 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   Report Post  
Dave O
 
Posts: n/a
Default Can all the sheets in one workbook all the same?

Forgot to add: please contact me, cyclezen atsign yahoo dot com, with
comments.



  #6   Report Post  
camalex03
 
Posts: n/a
Default 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   Report Post  
Dave O
 
Posts: n/a
Default 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   Report Post  
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
3 sheets in workbook, but visible only one? slaya_cz Excel Discussion (Misc queries) 3 September 30th 05 01:37 PM
Copy formatted in several sheets of a workbook Emece Excel Worksheet Functions 1 September 28th 05 10:10 PM
Macros for Protect/Unprotect all sheets in a workbook Paul Sheppard Excel Discussion (Misc queries) 2 August 4th 05 04:30 PM
Sending sheets in workbook billy2willy Excel Discussion (Misc queries) 4 July 28th 05 05:43 PM
Automatically copy selective sheets from one workbook to another Ann Excel Discussion (Misc queries) 0 March 1st 05 08:09 PM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"