Copy filtered data
 

Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows which has a "2" in column C. Let's say it leaves rows 1:4 and
8:10. Now I want to copy the filtered data in columns F:G and paste the values (not to an empty range which is easy) but to the same
cells in colums D:E.

Any help?

Hans Knudsen

Copy filtered data
 

Try Advanced Filter, excellent tutorial here from Debra Dalgeish, owner of
the site,
http://www.contextures.com/xladvfilter01.html#ApplyAF
Regards,
Alan.
"Hans Knudsen" wrote in message
...
Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows
which has a "2" in column C. Let's say it leaves rows 1:4 and 8:10. Now I
want to copy the filtered data in columns F:G and paste the values (not to
an empty range which is easy) but to the same cells in colums D:E.

Any help?

Hans Knudsen

Copy filtered data
 

This assumes that you applied the filter to the worksheet.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
If .AutoFilter.Range.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only the header row showing.
'do nothing
Else
With .AutoFilter.Range
Set myRng = Intersect(.Resize(.Rows.Count - 1) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible), _
.Parent.Range("F:G"))
For Each myArea In myRng.Areas
myArea.Offset(0, -2).Value = myArea.Value
Next myArea
End With
End If
End With

End Sub

Hans Knudsen wrote:

Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows which has a "2" in column C. Let's say it leaves rows 1:4 and
8:10. Now I want to copy the filtered data in columns F:G and paste the values (not to an empty range which is easy) but to the same
cells in colums D:E.

Any help?

Hans Knudsen

--

Dave Peterson

Copy filtered data
 

Just what I wanted!
Thank you very much Dave Peterson.

Hans Knudsen


"Dave Peterson" skrev i en meddelelse ...
This assumes that you applied the filter to the worksheet.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
If .AutoFilter.Range.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only the header row showing.
'do nothing
Else
With .AutoFilter.Range
Set myRng = Intersect(.Resize(.Rows.Count - 1) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible), _
.Parent.Range("F:G"))
For Each myArea In myRng.Areas
myArea.Offset(0, -2).Value = myArea.Value
Next myArea
End With
End If
End With

End Sub

Hans Knudsen wrote:

Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows which has a "2" in column C. Let's say it leaves rows 1:4
and
8:10. Now I want to copy the filtered data in columns F:G and paste the values (not to an empty range which is easy) but to the
same
cells in colums D:E.

Any help?

Hans Knudsen

--

Dave Peterson