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.
 
Precede that with Set

As in Set DestSheet = Sheets("Combined_Tax")

same goes for the SourceSheet...


Sorry bout that!
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Ok the code works now but there is a hitch. The code is skipping the....."DestSheet.Range(Cells("A65536").End(xlUp)).Value = SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W"))"

Then it loops through this part of the code continually....minus the DestSheet part.

If SourceSheet.Range("X" & cRow) = 0 Then
DestSheet.Range(Cells("A65536").End(xlUp)).Value = SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W"))
End If
Next x


This is my code now.


Dim cRow As Long
Dim DestSheet As Worksheet
Dim SourceSheet As Worksheet
cRow = Sheets("Local").Range("A1").End(xlDown).Row
Set DestSheet = Sheets("Combined_Tax")
Set SourceSheet = Sheets("Local")
Application.ScreenUpdating = False
For x = 1 To cRow
If SourceSheet.Range("X" & cRow) = 0 Then
DestSheet.Range(Cells("A65536").End(xlUp)).Value = SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W"))
End If
Next x
 
Upvote 0
Is the correct place for the values of 1 and 0 in column X?

The only time it would hit the DestSheet part is if the evaluations value in column X = zero

If the data in column X is text then change it to

"0" instead of 0
 
Upvote 0
Yes the correct column to look in is "X". The fomat of column X is "General" so it should be just 0 and 1. I have a couple of "0" in that column and it is not reading them. What is ....... "For x = "1" To cRow". I am not familar with this.
 
Upvote 0
The for X to Crow...

lets see

First I determine how many rows to loop through by finding the last row in the "cRow =" part

then what I do is set a counter in the for ..... next loop by cycling through each ROW # from 1 to cRow (which is number filled above)

it looks in that row, column X for the number 0...if that condition is met it copies the data from columns E to W into the other sheet at the next available row.

It increments the X value (or row number) (by way of the "next x" line )until the counter runs out.

Make a little more sense now?

Format the cell to number, and the text zeros won't get skipped.
 
Upvote 0
I changed the format to Number now. However it still does not work. It is just not reading the 0's
 
Upvote 0
Try this line and see if that helps...

I set up some dummy data and had no problem either way ....so i am not sure where the problem is on your end...but try this as an alternative.

If SourceSheet.Range("X" & cRow).Value*1 = 0
 
Upvote 0
Excel was expecting a "Then" or "GoTo" statement with that code. I have a feeling that with all of the changes that you have been helping me with I have messed up a part of the code. I dont know if you are using my code as your tester but if you aren't try mine to see if I am missing a variable or something.



Dim cRow As Long
Dim DestSheet As Worksheet
Dim SourceSheet As Worksheet
cRow = Sheets("Local").Range("A1").End(xlDown).Row
Set DestSheet = Sheets("Combined_Tax")
Set SourceSheet = Sheets("Local")
Application.ScreenUpdating = True
For x = 1 To cRow
If SourceSheet.Range("X" & cRow) = 0 Then
DestSheet.Range(Cells("A65536").End(xlUp)).Value = SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W"))
End If
Next x
 
Upvote 0
Dim cRow As Long
Dim DestSheet As Worksheet
Dim SourceSheet As Worksheet
cRow = Sheets("Local").Range("A1").End(xlDown).Row
Set DestSheet = Sheets("Combined_Tax")
Set SourceSheet = Sheets("Local")
Application.ScreenUpdating = True
For x = 1 To cRow
If SourceSheet.Range("X" & cRow).Value * 1 = 0 Then
DestSheet.Range(Cells("A65536").End(xlUp)).Value = SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W"))
End If
Next x
 
Upvote 0
alternate option

Dim cRow As Long
Dim DestSheet As Worksheet
Dim SourceSheet As Worksheet
cRow = Sheets("Local").Range("A1").End(xlDown).Row
Set DestSheet = Sheets("Combined_Tax")
Set SourceSheet = Sheets("Local")
Application.ScreenUpdating = True
For x = 1 To cRow
If SourceSheet.Range("X" & cRow).Value * 1 = 0 Then
SourceSheet.Range(Cells(cRow, "E"), Cells(cRow, "W")).Copy
DestSheet.Range(Cells("A65536").End(xlUp)).PasteSpecial (xlvalues)
Application.CutCopyMode = False
End If
Next x
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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