Solved: Copy selected range in autofilter

[jov_damo86]

I want to input the filtered data from selected cell(autofilter activated) into userform textbox.

example: after filtering(autofilter activated), I select a certain cell,lets say A3. it will input the data from A3,A4,A5,A6,A7 to userform textbox1,textbox2,textbox3,textbox4,and textbox7.
Take note: Autofilter activated

:help Looking for your help in VBA...Thanks.

[Kenneth Hobs]

If filtered, even if A4 is not shown, did you want it in textbox2? It is unclear what is needed. Is it a matter of selecting and that cell and the 4 below it or that cell and the next 4 visible cells or?

[jov_damo86]

Im sorry,I have mistake to what i want to ask.
to clarify my question,
1. lets say


.. A..|. B..|. C..|.D ..|.. E <===Column
1 AA | Aa | aA | aa | Ax
2 Bx | Bx | xB | bx | Bx
3 CC | Cc | cC | cc | Cx
4 DD | Dd | dD | dd | Dx
5 Ex | Ex | xE | xe | Ex

2. then i will filter a certain text like "x*" using column A1 then give like this


.. A..|. B..|. C..|.D ..|.. E
1 Bx | Bx | xB | bx | Bx
2 Ex | Ex | xE | xe | Ex


3. Now, I will pick and select A2 then
textbox1=A2.value,
textbox2=B2.value,
textbox3=C2.value,
textbox4=D2.value,
textbox5=E2.value

I cannot collect the correct data using vba because it is filtered. please help. thanks.

[Kenneth Hobs]

Userform1.Show to show the userform. Set the ShowModal property of Userform1 to False. Add the textbox controls, OKBttn, and CancelBttn

In a Module:
Sub FillTBs()
Dim r As Range, rw As Range, i As Long, j As Long
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
If r.Rows.Count < 1 Then Exit Sub
For Each rw In r.Rows
j = j + 1
If j = 2 Then
Set r = rw
Exit For
End If
Next rw
For i = 1 To 5
UserForm1.Controls("Textbox" & i).Value = Cells(r.Row, i)
Next i
End Sub

Right click the sheet tab, view code and paste:
Private Sub CancelBttn_Click()
Unload Me
End Sub

Private Sub OKBttn_Click()
FillTBs
End Sub

[jov_damo86]

I think you can only get the data of row 2 if i use that code. but thanks for that because i can manipulate that to attain what i need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range, rw As Range, i As Long, j As Long
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
If r.Rows.Count < 1 Then Exit Sub
For Each rw In r.Rows
j = j + 1
If j = Target.Row Then
Set r = rw
Exit For
End If
Next rw
For i = 1 To 5
UserForm1.Controls("Textbox" & i).Value = Cells(r.Row, i)
Next i
End sub

[Kenneth Hobs]

FillTBs can also be added to the sheet's Calculate event.

If that is all that you wanted then filtering is irrelevant.
Sub FillTBs()
Dim i As Integer
For i = 1 To 5
UserForm1.Controls("Textbox" & i).Value = Cells(Selection.Row, i)
Next i
End Sub