Macro Excel to copy filtered data to another sheet

Hi,

I'm very new into macro excel and I would like to copy an entire sheet after filtering and copy only the row with the name "USA"

it is a big worksheet and if I replace "rngDest.Value = rngSource.Value
" by "rngDest.Value = rngSource.SpecialCells(xlCellTypeVisible).Value" i get the first row replicated 1'000'000 times.

I hope you can help me
thanks

---

Sub copycontent()

Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim rngSource As Range, rngDest As Range

Set wbSource = Workbooks.Open("test.xlsx", , True)
Set wsSource = wbSource.Worksheets("Sheet1")
wsSource.Range("$A$1:$H$3").AutoFilter Field:=2, Criteria1:="USA"
Set rngSource = wsSource.Range("A:Z")

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets("Sheet1")
Set rngDest = wsDest.Range("A:Z")

rngDest.Value = rngSource.Value

wbSource.Close (False) '

End Sub

Untested):

Please Login or Register  to view this content.