Auto Filter / Copy-Paste Code

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
I have a spreadsheet that I am using an auto filter on. I need to copy the results of this filter into another spreadsheet. Basically I need to select, copy, and paste (Values) of a range.


Dim CopyRng As Range, PasteRng As Range
Set PasteRng = Sheets("Combined_Tax").Range("A65536").End(xlUp).Offset(1, 0)
With Sheets("Local")
Set CopyRng = .Range("A1", .Range("A1").SpecialCells(xlLastCell))
End With
CopyRng.Copy Destination:=PasteRng


I am not sure how to write the CopyRng. I need it to be only for the selection of the auto filter and only for the Columns (E:W). Also, how would I only paste "Values" using a "PasteRng"......... Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Have you tried using Advanced Filter with a second sheet as the destination?
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
I not not sure if that will work because the criteria that I am using to filter will not be copied. This is in a different column.
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Is the filter a manual entry type thing or is it constant in your data..rather, would you want to tie the filter into the copy issue (my thought is that it would be pretty simple to have code that simply looked for the filter requirement and pumped each matching row onto the new sheet...but only for the needed columns...

I have a number of such copy paste routines, but would rather put something out there that did just what you needed...
Just a quick untested edit of what you have may work....

With Sheets("Local")
Set CopyRng = .Range(65536,1).End(Xlup).Offset(1,0)
For each c in CopyRng
if c.Row.Visible = True then
Sheets("Combined_Tax").Range(cells(65536,1).End(xlup) = Sheets("Local").Range(cells(c.Row,"E"),cells(c.Row, "W") .Values
End If
Next c
End with
 

ECF1956

Board Regular
Joined
Mar 2, 2005
Messages
54

ADVERTISEMENT

This is a bit of code I used in a different application

Selection.AutoFilter Field:=7, Criteria1:=">= " & TODAYS, Operator:=xlAnd,_
Criteria2:="<= " & DATE6MO
Columns("A:L").SpecialCells(12).Copy

Paste this where you need to.

Change for your filter. Hope this helps.
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Basically what I have in Column X is a test. Instead of a "T" or "F" I had it return "1" and "0". I need to take all the "0" and append into another worksheet.

I am a beginner with codeing....i apoligize. I am not sure what "For each c in CopyRng" and "if c.Row.Visible = True then" are referencing. Everything else looks good. Can you explain this a little more to me. Thanks
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

Code:
For each c in CopyRng
--means for each item in the copy range, syntax is for each {item} in {object}


Code:
if c.Row.Visible = True then
--means if the row is visible then copy then perform the following.

the c identifies the item in question, in this case a Cell but it could be called anything. the C.Row says "give me the Row number of the cells address"

that row translates into the cell address used in the cells(C.Row,"W") type syntax.

Code:
Next c
-- means look at the next item in the object


However with this new information you can skip the autofilter part and just do this and you won't even have to filter the data:

Code:
Dim cRow as long
Dim DestSheet as Worksheet
Dim SourceSheet as Worksheet
cRow = Sheets("Local").Range("A1").End(xlDown).Row
DestSheet = Sheets("Combined_Tax")
SourceSheet = Sheets("Local")
Application.ScreenUpdating = False
For x = 1 to cRow
If SourceSheet.Range("X" & cRow) = 0 Then
DestSheet.Range("A65536").End(xlup) = SourceSheet.Range(cells(cRow,"E"), cells(cRow, "W").Value
End If
Next x

MsgBox "Copy Complete"

Application.ScreenUpdating = True
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Thanks for the explanation...I like to see how things work so that I am not always bothering people. I pasted the code into my macro. I got a compile error on this line of code:

DestSheet.Range(cells("A65536").End(xlup).Value = SourceSheet.Range(cells(cRow,"E"), cells(cRow, "W")


Does this have something to do with the "cells" in the code....Should it just be "Cell"?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
DestSheet.Range(cells("A65536").End(xlup)).Value = SourceSheet.Range(cells(cRow,"E"), cells(cRow, "W") )

try that instead
sorry, was missing closing parenthesis on both sides
 

bschulze

Active Member
Joined
Jun 2, 2005
Messages
289
Ok Excel accepted the code. But when I ran it I got an error.

"Run time error '91':"........"Object Variable or With block variable not set"




This was the piece of code that was highlighted in the debug

DestSheet = Sheets("Combined_Tax")



I cant spot the problem in the code...but that is not saying much. Like I said before I am just a beginner. If you have any ideas please let me know. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,380
Members
412,589
Latest member
ArtBOM
Top