Macros and Printing Footers

[greengrass]

I would like to get some help in adding page numbers and dates (footer)
on each page when I print this workbook using the following macro. I
have a total of 22 tabs in the workbook after the macro inserts the
individual workbooks in the workbook called mybook.

If this is possible can you please show me the way?

Thanks,

'Copy a sheet from each workbook into your workbook
'This macro will copy the first sheet of each workbook into the
workbook where the code is in.
'The sheet will be named as the workbook name..
Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
..NewSearch
..LookIn = "C:\Documents and Settings\Desktop\play 2006"
..SearchSubFolders = False
..FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End Sub

 

[Guest]

Hi greengrass,

What you're looking for is found in .PageSetup for each sheet. To add footer
info for page numbers and date, you'll need something like the following in
your code before you open the next file:

With ActiveSheet.PageSetup
.RightFooter = "&7 Page &P of &N" & Chr(10) & "&D"
End With

The result of the above code will be located right-aligned and will look
like this:

Page 1 of n
3/7/2006
where pages(n) is the number of pages of print for that sheet.

If you mean you want to number the sheets as in their indexes, you can use
the ActiveSheet.Index appended to a string value like "Sheet ", for example:

.LeftFooter = "Sheet " & ActiveSheet.Index &" of " & basebook.Sheets.Count

The index will increment for each sheet you copy into basebook.
Other choice: .CenterFooter = "something else"

As a reference, you can use the macro recorder to get a list of all the
properties used in PageSetup.
I hope this is helpful!
Regards,
GS