Common footer but not common margins please -(Page 1 of 2) etc

R

RajenRajput1

Hi.

I'm a little hacked off. :eek:) (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.
 
S

Sheeloo

When you have selected ALL SHEETS margins are bound to change to the same
setting for all of them along with header and footer.

You can first set header and footer and then set margins.

Or try to record a macro while setting header and footer, edit it to remove
anything other than setting of header and footer and run it when needed
 
S

Sheeloo

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
 
R

RajenRajput1

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 said:
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 said:
Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
 
G

Gord Dibben

You are overlooking the fact that grouped sheets take on the print
properties of the active sheet.

I would run this macro to set just the common footer and not upset the other
print properties.

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

Adjust to suit your custom footer.


Gord Dibben MS Excel MVP
 
R

RajenRajput1

Sheeloo,

Thank you, but I couldn't get it to work.

I tried the code you gave, and upon running the macro, testing it, it only
does one worksheet.

I tried to generate the code myself, the same thing as yours appeared, but
it did not work on re-run.

Any thoughts ?


Sheeloo said:
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 said:
Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
 
S

Sheeloo

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 said:
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 said:
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 said:
Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
Click to expand...
 
R

RajenRajput1

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



Sheeloo said:
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 said:
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 said:
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


:

Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
Click to expand...
 
R

RajenRajput1

All hail the Gord !

Thank you.

Gord Dibben said:
You are overlooking the fact that grouped sheets take on the print
properties of the active sheet.

I would run this macro to set just the common footer and not upset the other
print properties.

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

Adjust to suit your custom footer.


Gord Dibben MS Excel MVP

Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
 
G

Gord Dibben

I assume that means you can use the macro.

Thanks for the feedback.


Gord

All hail the Gord !

Thank you.

Gord Dibben said:
You are overlooking the fact that grouped sheets take on the print
properties of the active sheet.

I would run this macro to set just the common footer and not upset the other
print properties.

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

Adjust to suit your custom footer.


Gord Dibben MS Excel MVP

Hi.

I'm a little hacked off. :eek:) (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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing margins on 2nd page of letterhead template 4
Footer On Last Page Only Not Working 1
Header and Footer margins 1
How to save a PDF with header that extends to edge of page? 3
How 2 set margins 2 show when exceeded @ 'normal view' ??? 3
Margins, footers etc 7
Vertical ruler vs margins? 1
footnotes and page margin; footer margin and page margin 3

Top