The easiest way to explain it is that you will need to place code in the OnFormat event. The problem is, there are several OnFormat events. Different sections have an OnFormat event. As the Report renders, different sections are formatted. In other words, one section, like a detail header is formmated at a different time the Report footer section is formatted.
In the Page Footer On Format event, you should be able to have access to the Page and Pages property. So, I would start by placing some code in there to see if you can accomplish what you need. Maybe you can set the Visisble property of the Footer you want to affect to = No. Then, change it to visible via VBA code.
Understand that there are default names given to these sections. You may want to rename them before referencing them in your code and before creating any event handlers.
Code:
If me.Page = Me.Pages then
me.NameOfSection.Visible = True
end if