Macro Footer

[dennisli]

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

 

[tactps]

I am a little confused by your description, but it seems that each page has different page setups.

Can you post the macro?

 

[dennisli]

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]

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.