Custom Footer Macro

[Davis Henderson]

I am trying to create a custom footer based off parts of the sheet names. Below are the instructions for what I am trying to build. I recorded a macro that could pull the sheet name and page number, but as you can see I need more customization.

1. Create footers:
   1. Customer footer
   2. Right footer
   3. Format: Times New Roman, 10, Bold, Red
   4. 1. IF sheet name has ‘.’ = ['Sheetname before ‘.’' + '.' + 'Page number']
      2. IF sheet name doesn’t have ‘.’ = ['Sheet name before ‘ ‘' + '.' + 'Page number']

For example: sheetname= B2.1 Denver expenses; page number 5; FOOTER= B2.5 OR sheetname= B2 Denver Expenses; page number 3; FOOTER= B2.3

I recorded a macro that could pull the sheet name and page number, but as you can see I need more customization. I also can provide an example excel file.

If anyone could provide guidance or speak to the feasibility of this it would be more than appreciated!!

 

[p45cal]

The part of your code that prints the sheet name is in red below:
.RightFooter = "&""Times New Roman,Bold""&10&KFF0000&A&P"

Unfortunately, I think it would be very complicated to replace it with something that could track what sheet and page was being printed at printing time.
However, what could be done, is to rename the relevant sheets just before printing to a shorter version (everything before the dot), then restore the original names after printing, but I suspect that too could fall over as I strongly suspect that your sheets are named very similarly and it's the stuff after the dot that distinguishes them; this means Excel will be trying to rename multiple sheets with the same name, which it won't allow.
If that's not the case, then I can probably write some code to do it.