Auto Filter in VB

Jwoltz

New Member
Joined
Oct 17, 2006
Messages
4
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

New Member
Joined
Oct 17, 2006
Messages
4
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
What are you actually trying to do?
 

Jwoltz

New Member
Joined
Oct 17, 2006
Messages
4
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. :)
 

Forum statistics

Threads
1,141,144
Messages
5,704,544
Members
421,354
Latest member
Canary99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top