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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
75,831
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top