Copying on a filtered column

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Greetings,

I have a list of data in Column A through Column C.
Columns B and C contain data from which I will be
filtering the data. I need some code that would allow me
to copy the first 10 items in column A, and paste them to
Column A of a different sheet ("Sheet 2"). Obviously,
this is fairly simple if the filter is not applied, as I
can come up with code that would select the range (A2:A11)
and copy it over. However, when the filter is applied,
the range varies, because the filter hides various rows.
Even when the filter applies, I want the code to select
just the first ten items (which are not hidden by the
filter), and copy those over to sheet 2. I would
appreciate any help you could give. Thanks

Jason
 
Sub test5()
Dim rng As Range, rng2 As Range
Dim iCell As Range, i As Long
Set rng = Sheets(1).Range("A2:a100")
Set rng2 = Sheets(2).Range("A2")
Set partRng = rng.SpecialCells(xlCellTypeVisible)
i = 1
For Each iCell In partRng
rng2(i).Value = iCell.Value
If i = 10 Then Exit Sub
i = i + 1
Next
End Sub

Alan Beban
 
Back
Top