Help about macro and filtering data

[bitaz]

Dear all,
I need to make address database for my clients. I put a box in Sheet 2 so that I can print it out to glue it in an envelope. So I want the box to be contained automatically with the Company name and address that I select in filtered cell in Sheet 1. So I can only select the name of the Company in B1 (Sheet 1) and the Company name and address will show automatically inside the text box in Sheet 2. Please help about the macro. Giga thanks in advance.

[JBeaucaire]

1) Put this formula in E1 of Sheet1:

=IF(SUBTOTAL(109,A:A)>MAX(A:A),"",SUBTOTAL(109,A:A)+1)

2) Right-click the sheet1 tab and select VIEW CODE

3) Paste in the event macro:

Option Explicit

Private Sub Worksheet_Calculate()
    
    If [E1] = "" Then
        Sheets("Sheet2").Shapes(1).TextFrame.Characters.Text = ""
    Else
        With Range("A" & [E1])
            Sheets("Sheet2").Shapes(1).TextFrame.Characters.Text = _
              .Value & Chr(10) & .Offset(, 1).Value & Chr(10) & .Offset(, 2).Value
        End With
    End If
    
End Sub

4) Close the VBA editor and save your workbook as a .xlsm format so the macro will continue to work.

5) Now any changes the filter will update the text box on sheet2.

[bitaz]

Whoaaa! It does work.
Thank you sooooo much for helping. Thanks. :D :D