Solved: Copy and paste only visible characters

[CatDaddy]

I am trying to run a filter on a set of data, copy only whats left after the filter, then remove the filter to repeat with a different filter...my code is a little bulky but i dont see why it wont work


'ISOLATE BY MKP
Dim MKP As String
Dim i As Integer
Dim MKPRng As Range


MKP = "Search Criteria"
i = 3

ActiveWorkbook.Worksheets(1).Activate
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Range("A1").Activate
Set MKPRng = Selection.CurrentRegion
MKPRng.AutoFilter Field:=3, Criteria1:=MKP, Operator:=xlFilterValues

Set MKPRng = Selection.SpecialCells(xlCellTypeVisible)
MKPRng.Copy

ActiveWorkbook.Worksheets(i).Activate
Range("A:AL").Select
Selection.PasteSpecial Paste:=xlPasteValues

[Chabu]

I think it is because you change the active selection at the end.
try
MKPRng.Copy destination:= ActiveWorkbook.Worksheets(i).Range("A:AL").PasteSpecial Paste:=xlPasteValues

[CatDaddy]

I think it is because you change the active selection at the end.
try
MKPRng.Copy destination:= ActiveWorkbook.Worksheets(i).Range("A:AL").PasteSpecial Paste:=xlPasteValues

your suggestion makes sense but your code gives me a compile error "expected end of statement" for the paste part...

[p45cal]

try changing:
Set MKPRng = Selection.SpecialCells(xlCellTypeVisible)
to:
Set MKPRng = MKPRng.SpecialCells(xlCellTypeVisible)
(untested).

[Chabu]

I'm afraid I did not test it, there is no need for the pastespecial... at the end
mkprng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL")

[CatDaddy]

try changing:
Set MKPRng = Selection.SpecialCells(xlCellTypeVisible)
to:
Set MKPRng = MKPRng.SpecialCells(xlCellTypeVisible)
(untested).

with his change or on the original? thanks :)

[CatDaddy]

I'm afraid I did not test it, there is no need for the pastespecial... at the end
mkprng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL")

the pastespecial is to paste only the values, im not sure it will work without it

[CatDaddy]

Dim MKP As String
Dim i As Integer
Dim MKPRng As Range


MKP = "Criteria"
i = 3

ActiveWorkbook.Worksheets(1).Activate
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Range("A1").Activate
Set MKPRng = Selection.CurrentRegion
MKPRng.AutoFilter Field:=3, Criteria1:=MKP, Operator:=xlFilterValues

Set MKPRng = MKPRng.SpecialCells(xlCellTypeVisible)
MKPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL")
Sheets(i).Name = "Criteria"


SUCCESS! Thank you both!