Copy filtered data with vba

[JoeyGaspard]

I am using the following code to filter and delete data, I would like to be able to copy the filtered data, but instead of the "EntireRow", I want to copy range A:E of the filtered set only instead of deleting, to cell A1 of another worksheet and turn off the filter so I can do it again with a different code, can someone help me on how to do that? Thanks in Advance!!

With Sheets("Data")

.AutoFilterMode = False

With .Range("A1", .Range("A" & Rows.Count).End(xlUp))

.AutoFilter 1, "95802"

On Error Resume Next

.Offset(1).SpecialCells(12).EntireRow.Delete

End With

.AutoFilterMode = False

End With

 

[Fluff]

How about

Sub JoeyGaspard()
   With Sheets("Data")
      .AutoFilterMode = False
      .Range("A1").AutoFilter 1, "95802"
      .AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("Sheet1").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[JoeyGaspard]

How about

Sub JoeyGaspard()
   With Sheets("Data")
      .AutoFilterMode = False
      .Range("A1").AutoFilter 1, "95802"
      .AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("Sheet1").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

I did not work, it didnt error out, but it did not do the copy/paste

 

[Fluff]

Do you have any blank rows in your data?

 

[JoeyGaspard]

Do you have any blank rows in your data?

No

 

[Fluff]

If you step through the code using F8 until you get to this line

.AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("Sheet1").Range("A1")

then look at the data, is it filtered correctly & are there any visible rows?

 

[JoeyGaspard]

If you step through the code using F8 until you get to this line

.AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("Sheet1").Range("A1")

then look at the data, is it filtered correctly & are there any visible rows?

Well, I removed the code, copied and pasted it back in, and now for some reason this one line is turned red and says : Compile Error: Expected list separtor or )

.AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("USCold").Range("A1)

 

[Fluff]

You're missing a " before the final )

 

[JoeyGaspard]

You're missing a " before the final )

OK, I found the problem, the new data file I have has the columns slightly rearranged, I need to filter by Column "C" instead of Column "A", so I changed that here, but it is still filtering Column "A" ??


With Sheets("Data")
.AutoFilterMode = False
.Range("C1").AutoFilter 1, "95802"
.AutoFilter.Range.Columns("A:E").Offset(1).Copy Sheets("NOCSTx").Range("A1")
.AutoFilterMode = False

 

[Fluff]

It needs to be

 .Range("A1").AutoFilter 3, "95802"