Solved: Autofilter, copy and paste into new sheet problem

[OhGorgeous1]

Good day to you all

I have the following problem:

The below macro works great in that it autofilters the data and then copies and pastes the filtered data to a new excel sheet as long as the criteria is exact (example: N3H) but if I use a wildcard (example: N*H) then it does the autofilter section ok but then copies all the data and not the filtered data.

Can anyone please help!!

[vba]Sub AutoFilter_CopyPaste()

'activate sheet
Sheets("Data").Activate
'select rows
Rows("1:1048576").Select
' autofilter
Selection.AutoFilter Field:=7, Criteria1:=Worksheets("Menu").Range("C15").Value
'copy result
Selection.Copy
'activate sheet
Sheets("Results").Select
'paste values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'clear selection
Range("A1").Select
End Sub[/vba]

[mdmackillop]

Try
[VBA]
Sub AutoFilter_CopyPaste()
With Sheets("Data")
.Columns(7).AutoFilter Field:=1, Criteria1:=Worksheets("Menu").Range("C15").Value
.Cells.Copy
Sheets("Results").Range("A1").PasteSpecial Paste:=xlValues
.Columns(7).AutoFilter
Application.CutCopyMode = False
End With
Application.Goto Sheets("Results").Range("A1")
End Sub

[/VBA]

[OhGorgeous1]

Thanks for this but it kicks up on the line
[VBA]Sheets("Results").Range("A1").PasteSpecial Paste:=xlValues [/VBA]

[Bob Phillips]

It seems to work for me, although I would use xlPasteValues not xlValues, but I am not sure it is copying the correct data.

[OhGorgeous1]

All sorted now thanks for the help anyway, used my original code with the below added in

[VBA]'Message box to inform user the job is complete.
MsgBox "Your Search Is Complete.", 64, "Search Completed"
'activate sheet
Sheets("Data").Activate
' autofilter
Selection.AutoFilter
Sheets("Menu").Select[/VBA]

and it works a dream!