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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Have you tried using Advanced Filter with a second sheet as the destination?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,257
Messages
6,135,502
Members
449,944
Latest member
parag385

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
Back
Top