Table Filtered Cell to print in center of footer

[aggie81]

Want to take the first filtered cell in column V of table Production and print in the center of the footer based on the filter(s) I apply.

To look something like this

4" Begonia
Printed on Today's Date as dynamic value
Sales from January 8, 2025 thru May 31, 2025 as static text

I found two videos on YouTube but can't bring them together to get what I want.
1.

Lelia Gharani's print same text in the header
2.

Hagamoslo Simple select first cell highlight

I'm not very good at VBA but trying to learn based on something I want/need to save me some time in preparing a production book.
Thanks for reading and any help in appreciated
Lee

 

[Marc L]

According to the attachment with the Macro Recorder …​

Sub Macro1()
         Dim Rc As Range, L&
   With Sheet1
    For Each Rc In .ListObjects(1).DataBodyRange.Columns(22).Cells
      If Not Rc.EntireRow.Hidden Then Exit For
    Next
         L = InStr(.PageSetup.CenterFooter, vbLf)
      If Not Rc Is Nothing And L Then
        .PageSetup.CenterFooter = Rc.Text & Mid(.PageSetup.CenterFooter, L)
         Set Rc = Nothing
        .PrintPreview
      Else
         Beep
      End If
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

A VBA demonstration according to the table active filters :​

Sub Demo1()
   With Sheet1
   With .ListObjects(1).AutoFilter.Filters
        If .Item(20).On And .Item(21).On Then S$ = Replace$(.Item(21).Criteria1 & .Item(20).Criteria1, "=", " ")
   End With
        L& = InStr(.PageSetup.CenterFooter, vbLf)
     If L And S > "" Then
       .PageSetup.CenterFooter = S & Mid(.PageSetup.CenterFooter, L)
       .PrintPreview
     Else
        Beep
     End If
   End With
End Sub

You should Like it !​

​

 

[aggie81]

Thank you for helping .
The code works with the file on my pc with a change to the Sheet1 to match the Sheetx on the full workbook on my pc.

I hoped to have it work like Ms. Gharani's video code where the code ran each time before print. She had a line at the beginning with
Private Sub Workbook_BeforePrint(Cancel as Boolean)

I assigned a macro key to it and it works well in the full production book.
This is much more than I am capable of at this point with my VBA knowledge. I will learn a little each day.
Thanks

 

[Marc L]

She had a line at the beginning with Private Sub Workbook_BeforePrint(Cancel as Boolean)

You can do the same including this BeforePrint event procedure within ThisWorkbook module rather than using a macro key …​

​

 

[aggie81]

Also, thanks for showing more than one way to achieve the same result.
Take care.