Auto Filter / Copy-Paste Code

[bschulze]

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.

 

[XLGibbs]

Have you tried using Advanced Filter with a second sheet as the destination?

 

[bschulze]

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]

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]

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]

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]

For each c in CopyRng

--means for each item in the copy range, syntax is for each {item} in {object}

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.

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:

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]

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]

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]

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