Want to copy only fist and second row after applying autofilter in VBA if data is available after autofilter

[Arif_Tamboli]

I want to copy only first and second row after applying autofilter in VBA.
My code
row_no = ThisWorkbook.Sheets("Agent List").Range("B" & Application.Rows.Count).End(xlUp).Row
For I = 3 To row_no
AGENTNAME = ThisWorkbook.Sheets("Agent List").Cells(I, 2).Value
ThisWorkbook.Sheets("Main Data").Activate
ThisWorkbook.Sheets("Main Data").Range("A1:J1").AutoFilter Field:=7, Criteria1:=AGENTNAME
On Error Resume Next
below mentioned line code is copying all the visible rows
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Copy
I don't want that
just want first and second visible row if data is available

 

[Dryver14]

replace this line

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Copy

with

Range("A2:J3").copy

 

[Arif_Tamboli]

Not working I want first & second visible cell to copy.

 

[Dryver14]

Not sure what you mean, do you want the heading and the first visible row or the 2 rows beneath the heading

 

[Arif_Tamboli]

Not sure what you mean, do you want the heading and the first visible row or the 2 rows beneath the heading

I don't need heading, what I want is
After applying autofilter
if visible rows are more than 2. than I want to copy first two visible row without heading.
if only one row is available still I want to copy it.

But I don't want more than 2 visible rows.

 

[Dryver14]

I have looked into this and it involves looping through the visible cells which is a bit of coding I'm unfamiliar with, perhaps someone else can jump in.

a second suggestion and one I could be tempted by is to do the full copy to a different location then take the 2 top rows from the copy.

 

[Arif_Tamboli]

I have looked into this and it involves looping through the visible cells which is a bit of coding I'm unfamiliar with, perhaps someone else can jump in.

a second suggestion and one I could be tempted by is to do the full copy to a different location then take the 2 top rows from the copy.

A second suggestion, I am doing right now that way but want to modify that and reduce manual work.
Help me on this

 

[Dryver14]

I'm trying

you don't need to do it manually. put it somewhere out of the way for instance

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(2).Copy destination:= Range ("A1000000")
range ("A1000000:J1000001").copy

 

[Arif_Tamboli]

it will again lead to next loop

 

[Dryver14]

No sure what you mean. Sorry

All this is doing is copying everything to A1000000 then taking the top 2 rows to where ever the macro goes next

You should add the line before end sub: Range("A1000000").currentregion.delete