OR change

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

to

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

Bernie


"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