footer for chart sheets
 

Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for
each worksheet in workbook with file name and path.

Public Sub PathAndFileNameInFooter()
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next wsSht
End Sub

It works great except I get a "type mismatch" error for chart sheets. How
can I modify the code to also include chart sheets?
--
maryj

maryj,

A chart sheet is not a worksheet, so you have to use a variable
data type that includes both. Object does that.
"--------------------------------------------
Public Sub PathAndFileNameInFooter()
Dim objSht As Object
For Each objSht In ActiveWindow.SelectedSheets
objSht.PageSetup.LeftFooter = ActiveWorkbook.FullName"
Next 'objSht
Set objSht = Nothing
End Sub
'-------------------------------------------
or this
'------------------------------------------
Public Sub PathAndFileNameInFooter2()
Dim lngCount As Long
For lngCount = 1 To ActiveWindow.SelectedSheets.Count
Sheets(lngCount).PageSetup.LeftFooter = ActiveWorkbook.FullName
Next 'lngCount
End Sub
'-----------------------------------------

Jim Cone
San Francisco, USA



"maryj" wrote in message
...
Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for
each worksheet in workbook with file name and path.
Public Sub PathAndFileNameInFooter()
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next wsSht
End Sub
It works great except I get a "type mismatch" error for chart sheets. How
can I modify the code to also include chart sheets?
--
maryj

Thanks Jim. The workbook will have sheets with both data and charts and would
like to have one macro to enter the footer for all sheets at once. Tried
combining the 2 procedures into 1 but still got the "type mismatch" error.
Can the 2 be combined?
--
maryj


"Jim Cone" wrote:

maryj,

A chart sheet is not a worksheet, so you have to use a variable
data type that includes both. Object does that.
"--------------------------------------------
Public Sub PathAndFileNameInFooter()
Dim objSht As Object
For Each objSht In ActiveWindow.SelectedSheets
objSht.PageSetup.LeftFooter = ActiveWorkbook.FullName"
Next 'objSht
Set objSht = Nothing
End Sub
'-------------------------------------------
or this
'------------------------------------------
Public Sub PathAndFileNameInFooter2()
Dim lngCount As Long
For lngCount = 1 To ActiveWindow.SelectedSheets.Count
Sheets(lngCount).PageSetup.LeftFooter = ActiveWorkbook.FullName
Next 'lngCount
End Sub
'-----------------------------------------

Jim Cone
San Francisco, USA



"maryj" wrote in message
...
Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for
each worksheet in workbook with file name and path.
Public Sub PathAndFileNameInFooter()
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next wsSht
End Sub
It works great except I get a "type mismatch" error for chart sheets. How
can I modify the code to also include chart sheets?
--
maryj

maryj,
Not sure I understand what is going on.
Either one of the subs should work for both worksheets and chart sheets.
They are independent, choose one or the other.

Jim Cone
San Francisco, USA


"maryj" wrote in message
...
Thanks Jim. The workbook will have sheets with both data and charts and would
like to have one macro to enter the footer for all sheets at once. Tried
combining the 2 procedures into 1 but still got the "type mismatch" error.
Can the 2 be combined?
--
maryj


"Jim Cone" wrote:
maryj,
A chart sheet is not a worksheet, so you have to use a variable
data type that includes both. Object does that.
"--------------------------------------------
Public Sub PathAndFileNameInFooter()
Dim objSht As Object
For Each objSht In ActiveWindow.SelectedSheets
objSht.PageSetup.LeftFooter = ActiveWorkbook.FullName"
Next 'objSht
Set objSht = Nothing
End Sub
'-------------------------------------------
or this
'------------------------------------------
Public Sub PathAndFileNameInFooter2()
Dim lngCount As Long
For lngCount = 1 To ActiveWindow.SelectedSheets.Count
Sheets(lngCount).PageSetup.LeftFooter = ActiveWorkbook.FullName
Next 'lngCount
End Sub
'-----------------------------------------
Jim Cone
San Francisco, USA



"maryj" wrote in message
...
Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for
each worksheet in workbook with file name and path.
Public Sub PathAndFileNameInFooter()
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next wsSht
End Sub
It works great except I get a "type mismatch" error for chart sheets. How
can I modify the code to also include chart sheets?
maryj

Thanks Jim. My error. I used the first procedure and it works great.
--
maryj


"Jim Cone" wrote:

maryj,
Not sure I understand what is going on.
Either one of the subs should work for both worksheets and chart sheets.
They are independent, choose one or the other.

Jim Cone
San Francisco, USA


"maryj" wrote in message
...
Thanks Jim. The workbook will have sheets with both data and charts and would
like to have one macro to enter the footer for all sheets at once. Tried
combining the 2 procedures into 1 but still got the "type mismatch" error.
Can the 2 be combined?
--
maryj


"Jim Cone" wrote:
maryj,
A chart sheet is not a worksheet, so you have to use a variable
data type that includes both. Object does that.
"--------------------------------------------
Public Sub PathAndFileNameInFooter()
Dim objSht As Object
For Each objSht In ActiveWindow.SelectedSheets
objSht.PageSetup.LeftFooter = ActiveWorkbook.FullName"
Next 'objSht
Set objSht = Nothing
End Sub
'-------------------------------------------
or this
'------------------------------------------
Public Sub PathAndFileNameInFooter2()
Dim lngCount As Long
For lngCount = 1 To ActiveWindow.SelectedSheets.Count
Sheets(lngCount).PageSetup.LeftFooter = ActiveWorkbook.FullName
Next 'lngCount
End Sub
'-----------------------------------------
Jim Cone
San Francisco, USA



"maryj" wrote in message
...
Using Win 2K/Office 2K. Added this code to Personal.xls to create footer for
each worksheet in workbook with file name and path.
Public Sub PathAndFileNameInFooter()
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next wsSht
End Sub
It works great except I get a "type mismatch" error for chart sheets. How
can I modify the code to also include chart sheets?
maryj