Can a page number be displayed in a cell?

  • Thread starter Thread starter jdwyer
  • Start date Start date
J

jdwyer

I am trying to lookup a value in a different sheet, and insead of having
excel return specific values within that sheet, I would like for it to return
the Footer value. Is this possible?
 
Yes, you can do this. You just need a little VBA code:
Sub HeaderFooterSummary()
Dim ws As Worksheet, l As Long, PSinfo(1 To 7) As String

Application.ScreenUpdating = False
'prevent screen flicker during processing
With Sheet1
'amend sheet as required or add a new one if needed
l = 1
'the row counter
.Cells(l, 1).Value = "Sheet Name"
.Cells(l, 2).Value = "Left Header"
.Cells(l, 3).Value = "Centre Header"
.Cells(l, 4).Value = "Right Header"
.Cells(l, 5).Value = "Left Footer"
.Cells(l, 6).Value = "Centre Footer"
.Cells(l, 7).Value = "Right Footer"
'the headings
For Each ws In Worksheets
l = l + 1
With ws
PSinfo(1) = .Name
'the sheet name
With .PageSetup
PSinfo(2) = .LeftHeader
PSinfo(3) = .CenterHeader
PSinfo(4) = .RightHeader
PSinfo(5) = .LeftFooter
PSinfo(6) = .CenterFooter
PSinfo(7) = .RightFooter
End With
'the pagesetup info
End With
.Range(.Cells(l, 1), .Cells(l, 7)).Value = PSinfo
'pass array info to range on summary sheet
Next
.Columns("A:G").AutoFit
'resize columns as needed
End With
Application.ScreenUpdating = True

End Sub

If you don't know much about VBA, or know nothing about it, take a look at
this:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro

Regards,
Ryan---
 
Back
Top