Arif_Tamboli
New Member
- Joined
- Mar 1, 2018
- Messages
- 34
- Office Version
- 2016
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
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