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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,905
Members
431,772
Latest member
dannyboi1

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