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]

Precede that with Set

As in Set DestSheet = Sheets("Combined_Tax")

same goes for the SourceSheet...


Sorry bout that!

 

[bschulze]

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

 

[XLGibbs]

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

 

[bschulze]

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.

 

[XLGibbs]

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.

 

[bschulze]

I changed the format to Number now. However it still does not work. It is just not reading the 0's

 

[XLGibbs]

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

 

[bschulze]

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

 

[XLGibbs]

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

 

[XLGibbs]

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