PDA

View Full Version : Solved: Copy and paste only visible characters



CatDaddy
06-01-2011, 03:47 PM
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
06-02-2011, 02:34 AM
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
06-02-2011, 10:25 AM
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
06-02-2011, 10:40 AM
try changing:
Set MKPRng = Selection.SpecialCells(xlCellTypeVisible)
to:
Set MKPRng = MKPRng.SpecialCells(xlCellTypeVisible)
(untested).

Chabu
06-02-2011, 10:45 AM
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
06-02-2011, 10:47 AM
try changing:
Set MKPRng = Selection.SpecialCells(xlCellTypeVisible)
to:
Set MKPRng = MKPRng.SpecialCells(xlCellTypeVisible)
(untested).

with his change or on the original? thanks :)

CatDaddy
06-02-2011, 10:49 AM
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
06-02-2011, 11:01 AM
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!