1

I have the following code in VBA:

    If Sheets("Sheet1").Cells(i, "C").Value = "DOG" And Sheets("Sheet1").Cells(i, "D").Value = "TOY" Then
Sheets("Sheet1").Cells(i, "A").Copy Destination:=Sheets("TOYS").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
    If Sheets("Sheet1").Cells(i, "C").Value = "CAT" And Sheets("Sheet1").Cells(i, "D").Value = "TOY" Then
Sheets("Sheet1").Cells(i, "A").Copy Destination:=Sheets("TOYS").Range("J" & Rows.Count).End(xlUp).Offset(1)
End If

Instead of selecting just column "A" I would like to know if there is a simple way of selecting columns "A to H".

Thank you very much for your time.

Community
  • 1
  • 1
Rufus Long
  • 33
  • 2
  • 2
    First Filter on `Dog` and then copy to col A. Then filter on `Cat` and then copy to J and so on so forth. [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) will get you started. – Siddharth Rout Jun 14 '15 at 16:34

1 Answers1

0

You can change Sheets("Sheet1").Cells(i, "A").Copy to:

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(i, "A"),Sheets("Sheet1").Cells(i, "H")).Copy

To reduce the number of times you need to include the worksheet reference, you can use With ... End With:

With Sheets("Sheet1")
    .Range(.Cells(i, "A"),.Cells(i, "H")).Copy
End With

Note that there is a full stop before each Cells method.

ChipsLetten
  • 2,923
  • 1
  • 11
  • 28