Header/Footer How to populate Header with Text and Cell Value together

My day to day usage of Excel is very basic therefore any help is greatly appreciated:

My Access application exports data into a workbook. The data is generated by a query in Access that exports several fields using parameters. Two of the selections are “Fiscal Year” and “Fund Number”.

All the data is on worksheet “Data” while worksheet “CPAprint” contains the data formatted for printing. I wish to place the text “Fund Number” plus the actual “fund number” from the “Data” worksheet cell containing that information into the left header (Worksheet Header not column header) of the worksheet “CPAprint” as well as the text “Fiscal Year” plus the actual “fiscal year” from the “Data” worksheet into the right header of the worksheet “CPAprint”.

What is the best practice procedure to accomplish this?

Thanks to both responders...

Davesexcel: I really need to take some time and understand all your code... Wow

Arkadi: For someone like me, your solution looks like something I can understand. Where exactly does this code go?

Sorry for questions that are probably elementary but I'm afraid that's my level of understanding at this point...

I won't be able to try anything out until tonight (just been called out) but I will get back and check off as solved once I give it a try.

Thanks so much!

Arkadi,

Very kind of you to offer but now that I've had a moment to give your advice a go, I came up with the following and placed it in the ThisWorkbook Object and it works perfectly:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("CPAprint").PageSetup.LeftHeader = "Fund Number " & Worksheets("Data").Range("H2").Value
End Sub

This now brings up another question - how do I format the result so it matches the Center Header which is static text? I assume more code in the same sub routine?

Arkadi,

Thank you once again for the outstanding help.

I agree with both you and davesexcel regarding the use of the macro recorder... Having never used it in the past, I looked at davesexcel site and was simply blown away by all the information provided there - very generous. I tried it out late last night and really need to give it a fair chance when both eyes are open.

At this point I simply wanted to solve my issue with the header and struggle forward with my Access project and for that opportunity, I thank you both. I will most certainly delve into the Excel ether when I have some down time - I'm always up for learning.

I will post this issue as solved. Cheers!