Row 1 is the headers - in my tests, I used blanks. IF you never want the
first row, you can fix that by changing

..Columns(7).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("G2")


to

..Columns(7).Offset(1,0).SpecialCells(xlCellTypeVi sible).Copy
Sheets("Sheet2").Range("G2")

HTH,
Bernie
MS Excel MVP




"damorrison" wrote in message
ups.com...
thank you very much for this option, there is some flaw though,

It wants to paste the first item on the list in row 1 sheet1 all the
time into sheet2 column g then it pastes the correct data below


Sub Button1_Click()

Worksheets("Sheet2").Range("G:G").ClearContents
With Sheets("Sheet1").Range("A2").CurrentRegion
.AutoFilter Field:=1,
Criteria1:=Worksheets("Sheet2").Range("A2").Value
.AutoFilter Field:=2,
Criteria1:=Worksheets("Sheet2").Range("B2").Value
.AutoFilter Field:=3,
Criteria1:=Worksheets("Sheet2").Range("C2").Value
.AutoFilter Field:=4,
Criteria1:=Worksheets("Sheet2").Range("D2").Value
.AutoFilter Field:=5,
Criteria1:=Worksheets("Sheet2").Range("E2").Value
.AutoFilter Field:=6,
Criteria1:=Worksheets("Sheet2").Range("F2").Value
.Columns(7).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("G2")
.AutoFilter
End With
End Sub