PDA

View Full Version : Solved: Autofilter, copy and paste into new sheet problem



OhGorgeous1
03-17-2009, 04:36 AM
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!!

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

mdmackillop
03-17-2009, 02:05 PM
Try

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

OhGorgeous1
03-18-2009, 03:30 AM
Thanks for this but it kicks up on the line
Sheets("Results").Range("A1").PasteSpecial Paste:=xlValues

Bob Phillips
03-18-2009, 03:53 AM
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
03-18-2009, 04:25 AM
All sorted now thanks for the help anyway, used my original code with the below added in

'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

and it works a dream!