Variable in footer

F

Francis Hookam

Variable in footer

I have =COUNTA(Bx:Bxx) in the spreadsheet in, let us say, A1 - it varies as
the number of rows change - I am counting surnames of members of a club in
this case (it does not matter if the same total appears on more than one
page)

Can the value in A1 (or any cell) be displayed in the footer so that at each
printing it is updated according to the state of the worksheet at the time?

Francis Hookham
 
J

JE McGimpsey

Francis Hookam said:
Variable in footer

I have =COUNTA(Bx:Bxx) in the spreadsheet in, let us say, A1 - it varies as
the number of rows change - I am counting surnames of members of a club in
this case (it does not matter if the same total appears on more than one
page)

Can the value in A1 (or any cell) be displayed in the footer so that at each
printing it is updated according to the state of the worksheet at the time?

Put something like this in the ThisWorkbook code module (CTRL-click on
the workbook title bar and choose View Code):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
With ws
If .Name = "Sheet1" Then _
.PageSetup.LeftFooter = "Total: " & .Range("A1").Value
End With
Next ws
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top