Macro Footer

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
As nobody answers my question I posted before, I post my questions again. Thanks.

Within one workbook, there are three worksheets. In each worksheet, there are more two or more than two pages.
I tried to use Macro to show at the bottom of each page, the Left Footer shows like Page 1 of Sheet3, Sheet 3 is the third sheet of the workbook named Test. The Right Footer shows like Page 10 of Test. Test is the workbook name. Page 10 is sequenced from the first page of Sheet1 to the first page of Sheet 3. But I could not get it.
Can some guys give some advice?

Thanks lot.
Dennis
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
I am a little confused by your description, but it seems that each page has different page setups.

Can you post the macro?
 

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
Thanks,
the following code is copied from Nate Olive.

Sub sdfdsmfkdsjl2()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks("Tab.xls")
Application.ScreenUpdating = False
For Each ws In wb.Worksheets
With ws
.PageSetup.LeftFooter = "Page &P of &A"
.PageSetup.RightFooter = "Page &P of &F"
End With
Next
With wb
Application.Goto .Sheets(1).[a1]
.Sheets.Select False
End With
ActiveWindow.SelectedSheets.PrintOut
wb.Sheets(1).Select True
Set wb = Nothing
Application.ScreenUpdating = True
End Sub

This code only show the cumulative page count from the first page to last page for worksheet and workbook.
My purpose is: For example, there is one workbook which cotains worksheets. The first worksheet contains two pages. I just want to show like Page 1 of Sheet 1, Page 2 of Sheet1. For the next workbook, I want to show Page 1 of Sheet 2, etc. But for the whole book, I want to show like Page 1, of workbook, Page 2 of workbook, Page 3 of workbook.
Is it clear for you?
Thans.
Good night.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Try this:

Sub sdfdsmfkdsjl2()
Dim wb As Workbook, ws As Worksheet
Dim CountPage As Integer
Set wb = Workbooks(1)
Worksheets("Sheet1").Select
Set ws = ActiveSheet
CountPage = 0
Counter = 1
With ws.PageSetup
.LeftFooter = "Page &P of &A"
.RightFooter = "Page &P of &F"
End With
Do Until Counter = Worksheets.Count
CountPage = CountPage + Val(ActiveSheet.VPageBreaks.Count) + 1
ActiveSheet.Next.Select
Set ws = ActiveSheet
With ws.PageSetup
.LeftFooter = "Page &P-" & CountPage & " of &A"
.RightFooter = "Page &P of &F"
Counter = Counter + 1
CountPage = CountPage + Val(ActiveSheet.VPageBreaks.Count) - 1
End With
Loop
End Sub

You may need to adjust the "+1" or "-1" to get it to work properly.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top