Copying rows to another worksheet

The rows from the "DATA" sheet have to be copied to the "Quadrant2" sheet when criteria="Quadrant2" in the "DATA" sheet. Currently there are no rows that match this criteria in the "DATA" sheet. So only the column labels are being copied in each every row in the "Quadrant2" worksheet. How do I fix this issue? Here is the code snippet that I am using currently


Sheets("DATA").Activate

Range("a1:cj" & a).Select
Selection.AutoFilter field:=85, Criteria1:="Quadrant2"

Selection.Copy
Sheets("Quadrant2").Range("a1:cj" & a).PasteSpecial xlPasteValues

Hello friend,

I have a couple of questions. In the code snippet you provided there is a variable "a". What is that for? Is it part of a loop? Auto Filtering works but it may not be necessary.

Let me repeat what I think you want. You want to be able to search for the value "Quadrant2" in the worksheet "Data", presumably in a specific column, and copy the entire row to the worksheet "Quadrant2". If this is correct what is the range that the value "Quadrant2" will be in?

Let me know.

Thank you for your reply. The variable 'a' is the row count. The criteria "Quadrant2 has to be checked in column CH in the DATA sheet. The problem I am facing right now is that currently there are no cells which match criteria "Quadrant2" in the DATA sheet. So it is copying the column headers in the range ("a1:ci" & a) in all the cells in the Quadrant2 worksheet.

a = Sheets("DATA").UsedRange.Rows.Count

Sheets("DATA").Activate

Selection.AutoFilter field:=85, Criteria1:="Quadrant2"
Range("a1:ci" & a).Select
Selection.Copy
Sheets("Quadrant2").Range("a1:ci" & a).PasteSpecial

Thanks in advance

This should do for you. This doesnt set up a header or make any cell width changes it purely copies the data. I assumed that you needed only columns A through CH copied. If this is incorrect you can adjust it in code. Also i assumed that you had data in column A. If not then we are going to have to edit the code. If you want help getting the macro to make the worksheet look better attach a copy of your workbook and I can get it going for you. Here is code.

Please Login or Register  to view this content.

Let me know.

Thank You! It worked fine