Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and Printing Footers
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 -- greengrass ------------------------------------------------------------------------ greengrass's Profile: http://www.excelforum.com/member.php...o&userid=23119 View this thread: http://www.excelforum.com/showthread...hreadid=519811 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros and Printing Footers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Footers/Headers Not Printing | New Users to Excel | |||
Does anyone know if I can suppress footers when printing? | Excel Discussion (Misc queries) | |||
Printing footers | Excel Discussion (Misc queries) | |||
Printing Headers & Footers | Excel Discussion (Misc queries) | |||
Code for printing footers | Excel Programming |