Auto Filter in VB

[Jwoltz]

I'm having a little trouble with the Auto Filter Copy and Paste option in my VBA Code. I am using it to filter for unique records in place, but the trouble is, it keeps failing at the point where the data would be pasted. Here is a sample of my code. Any ideas?

Sheets("Divrec_30_t").Select
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Test Filter"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 9999
.PrintErrors = xlPrintErrorsDisplayed
End With
Columns("A:N").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ActiveWindow.LargeScroll ToRight:=-1
Cells.Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Selection.ClearContents
ActiveSheet.Paste <----- This is the point of failure.

Any help is much appreciated.

 

[Von Pookie]

You're using Advanced filter, for the record. Not autofilter. Two different things

At any rate: don't set CutCopyMode to false before pasting. You essentially cancel the copy. Edit: Though it appears that the ShowAllData and ClearContents will cancel the copy, as well, if those statements occur before you paste.

 

[Jwoltz]

You're using Advanced filter, for the record. Not autofilter. Two different things

At any rate: don't set CutCopyMode to false before pasting. You essentially cancel the copy. Edit: Though it appears that the ShowAllData and ClearContents will cancel the copy, as well, if those statements occur before you paste.

I tried using the code that you gave me and keep getting a error. It says that the Copy and paste area cannot overlap unless they are the same size and shape. It appears to get closer to what I need but, I think the show all data needs to be there, but not necissarily the clear contents.

 

[Von Pookie]

Yeah, that was an error on my part. The code I originally posted wasn't resetting the filter (I had omitted the "ShowAllData" statement). That code I posted wasn't that great--I deleted it but apparently not before you saw. Sorry about that.

I did just have a thought, though. You seem to be trying to delete the duplicate data and only end up with the unique, correct? You can do that without the copy/paste, by using ranges. See if this helps--I posted code in this thread and I believe the comments should explain things:
http://www.mrexcel.com/board2/viewtopic.php?t=230444

 

[Norie]

What are you actually trying to do?

 

[Jwoltz]

Yeah, that was an error on my part. The code I originally posted wasn't resetting the filter (I had omitted the "ShowAllData" statement). That code I posted wasn't that great--I deleted it but apparently not before you saw. Sorry about that.

I did just have a thought, though. You seem to be trying to delete the duplicate data and only end up with the unique, correct? You can do that without the copy/paste, by using ranges. See if this helps--I posted code in this thread and I believe the comments should explain things:
http://www.mrexcel.com/board2/viewtopic.php?t=230444

Ok, That worked. Thanks for the help.