Solved: Copy and paste only visible characters

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

[VBA]
'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
[/VBA]

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

Quote:

---

Originally Posted by Chabu

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

---

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

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

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

Quote:

---

Originally Posted by p45cal

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

---

with his change or on the original? thanks :)

Quote:

---

Originally Posted by Chabu

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

---

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

[VBA]
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"
[/VBA]

SUCCESS! Thank you both!