Megaman1982


Hi all, i'm pretty new to Excell in the workplace and would really love it if you lot could help me with this difficult (TO ME) macro i'm struggling with.

I'm trying to finish off this macro, or mod it so that i get the page number, tab number, a cell reference in the respective headers and footers as explained in the macro below. only thing is i don't really want the tab label, cell reference (rev number/date) on the first page ie the first tab called coversheet.

The text in bold italics is my attempt at this but i'm stuck at it.

Please guys i'd appreciate the help, who knows i might actaully get good at this one day!

Cheers, Tom

MACRO....;

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS In Worksheets
'REV number and date in RHS footer on all except cover sheet
If Not WS.Name = "Cover sheet" Then
WS.PageSetup.RightFooter = Worksheets("Cover sheet").Range("B55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("B56").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A56").Value
'Document number in LHS header size 14
WS.PageSetup.LeftHeader = Worksheets("Cover sheet").Range("A10").Value
'Tab name in RHS Header BOLD fontsize 20
WS.PageSetup.RightHeader = "&B&25&A"
'Page number in Central footer BOLD size 14
WS.PageSetup.CenterFooter = "&B&14&P"

End If
Next WS
End Sub





Re: Setting a header to all pages except the cover sheet

Megaman1982


Is it too difficult then







Re: Setting a header to all pages except the cover sheet

Cringing Dragon

For me, that seems to work. The sheet name must be EXACTLY as you have in quotes, though, and it IS case sensitive. Is the name of your cover sheet EXACTLY Cover sheet (with the C in upper case and everything else lower, and a space between Cover and sheet)

Note if the page setup for the cover sheet is already set to do the header and footer, this won't clear it. Manually change those settings to what you want and then run the macro again. Step through the macro rather than running it, to see whether it's doing the right thing at your If statement.







Re: Setting a header to all pages except the cover sheet

Megaman1982

The Sheet labels were exactly as written,

I sort of got around it by "cheating", I ran the macro then deleted it. this put the relevant stuff on all pages, i then removed the headers and footers manually from the front page.

Bit of a go around but i was in a dead end.






Re: Setting a header to all pages except the cover sheet

Cringing Dragon

Apologies - I mentioned that your macro would not clear any existing headers and footers on the first sheet, but I didn't tell you how to make it do that. If you want some of the headers and footers, but not all of them, then modify the Else section to replace the = "" line(s) with the same code as the Then section.

Code Block

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS In Worksheets
'REV number and date in RHS footer on all except cover sheet
If Not WS.Name = "Cover sheet" Then
WS.PageSetup.RightFooter = Worksheets("Cover sheet").Range("B55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("B56").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A55").Value & _
Worksheets("Cover sheet").Range("L35").Value & _
Worksheets("Cover sheet").Range("A56").Value
'Document number in LHS header size 14
WS.PageSetup.LeftHeader = Worksheets("Cover sheet").Range("A10").Value
'Tab name in RHS Header BOLD fontsize 20
WS.PageSetup.RightHeader = "&B&25&A"
'Page number in Central footer BOLD size 14
WS.PageSetup.CenterFooter = "&B&14&P"

Else

'Clear right footer
WS.PageSetup.RightFooter = ""
'Clear left header
WS.PageSetup.LeftHeader = ""
'Clear right header
WS.PageSetup.RightHeader = ""
'Clear central footer
WS.PageSetup.CenterFooter = ""

End If
Next WS
End Sub

Note I have tested this and it works for me in Excel 2003. If your code still applies the headers and footers to the cover sheet, then I'm guessing there must be something not quite right with the name of the cover sheet.

To troubleshoot, copy the code to a module and name it test() (because it won't let you step through the beforeprint event sub). Try stepping through the code ([F8] from the VBA editor, each time you hit [F8] it steps you to the next line). When you reach the line just after "For Each...", hover your cursor over "WS.Name". (NB I assumed the first sheet would be the cover sheet, if it isn't, [F8] until it loops to the cover sheet.) Does it display exactly the same as your If statement (eg "Cover sheet") If it doesn't, that's the problem. Change your If statement to match. If it does match, then keep [F8]ing - it should jump the Then section and step into the Else section. If it doesn't, then it must mean there's a difference between the actual sheet name and what's in the IF statement. If it does jump to the Else section, but still doesn't clear the headers, I don't know why but telling me what it does do might help me assist you. If it works when stepping through this way, but doesn't work before printing, then it could be that the sub is in the wrong place (it needs to be in "ThisWorkbook", not in a module or on a sheet), or that there is another event counteracting it - check the code on all the objects and modules in your project.






Re: Setting a header to all pages except the cover sheet

Mezhick

Also, as an advice. You can rename a class of the Sheet named "Cover Sheet". Enter VBE -> click on the sheet (something like this: "Sheet2 (Cover sheet)") -> at the properties window select "(name)" property (at the top) -> replace existing text with a new one ("CoverSheet"). Then you code needs to be changed like this one:

Code Block

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS In Worksheets
'REV number and date in RHS footer on all except cover sheet
If Not WS Is CoverSheet Then
WS.PageSetup.RightFooter = CoverSheet.Range("B55").Value & _
CoverSheet.Range("L35").Value & _
CoverSheet.Range("B56").Value & _
CoverSheet.Range("L35").Value & _
CoverSheet.Range("A55").Value & _
CoverSheet.Range("L35").Value & _
CoverSheet.Range("A56").Value
'Document number in LHS header size 14
WS.PageSetup.LeftHeader = CoverSheet.Range("A10").Value
'Tab name in RHS Header BOLD fontsize 20
WS.PageSetup.RightHeader = "&B&25&A"
'Page number in Central footer BOLD size 14
WS.PageSetup.CenterFooter = "&B&14&P"

End If
Next WS
End Sub

But your code must be executable too. Try to copy sheet name from the sheet label at the bottom of the excel window and paste it into the Worksheets("Cover sheet"). I had some mistakes because in russian letter "C" (spells like "S") and english letter "C" have the same way of writing.






Re: Setting a header to all pages except the cover sheet

Megaman1982

Cheers, it works a treat on the doc. I can put it to good use in the future. You've made me look pretty good infront of the old boys at work.

thanks again

Tom