Thank you again. The code will be useful for the future.



"Sheeloo" wrote:

Apparently MS has not separated the header/footer settings with margins...

Try ASAP Utilities. It may have a function which you want.

The number of worksheets that a Worksheets or a Sheets collection contains
is represented by the Count property. Based on this, Worksheets.Count or
Sheets.Count can give you the number of worksheets in the current workbook.

You will have to loop through the sheets to set the desired options.

I will try to give you the complete code sometime later today.

"RajenRajput1" wrote:

Thank you for your help on this.

So I cannot do the task (as simple as it seems) without the help of VB/ macro?

What is the code for VB to detect the total number of worksheets in the
workbook? With this code, I may be able to compile something more adaptable.


In a way, I am glad I wasn't silly enough to overlook anything. On the
otherhand, it's one of those things that people expect to be a feature in an
expensive software package. Imagine the people who compile a workbook with
dozens of sheets and pages, and then margin them all up how they want it,
then put a common footer, and bang, all the alignment is off - and there is
no undo for this.


Thank you, kind regards.


"Sheeloo" wrote:

With record Macro you will get something like the following;
(remove anything which you don't want. to get to the code choose Macros and
Edit)
Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Page &P of &N"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
End Sub


"RajenRajput1" wrote:

Hi.

I'm a little hacked off. :o) (In a nice way)

Using Excel 2007.

My margins keep resetting after putting in footers for the whole workbook in
one go.

Say sheet 1 has a margin of 1 inch from the top.

Say sheet 2 has margin 5 inch from the top.

When I select all sheets, and then add a footer like "Page &[Page] of
&[Pages]", then do print preview, the footers are correct, but the margins
change to be the same common size as the original sheet where I did the
changes.

What am I overlooking here? I only want to make a common footer, not a
common margin or page layout.

Any help would be appreciated, and I will give further info if necc.

Thank you very much.