copying a whole row

[chris_mayer]

Hi

I have the following code which copies the row from once sheet and copies it into another based on a value in a particular cell:

For r = 1 To lastRow
If LCase(sh1.Cells(r, 1)) = week Then
rowSh2 = rowSh2 + 1
sh1.Rows(r).Copy
sh2.Select
sh2.Cells(rowSh2, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

weekTwo = True

End If
Next

However, I don't want the whole row to be copied, only the first 23 columns. This is becuase I want to add data to the copied row in the new sheet and at the moment it wipes out all this data if I run the macro again.

How can I code it so that it only copies a certain amount of columns in the row I am copying.

Many Thanks

Chris

[Leith Ross]

Hello Chris,

You can define which cells to copy by defining the Range.

Change: sh1.Rows(r).Copy
To: sh1.Range(Cells(r, 1), Cells(1, 23)).Copy
This will copy Columns "A" through "W" only of row "r".

Sincerely,
Leith Ross

[ska67can]

This will do it

Please Login or Register  to view this content.

ska

[chris_mayer]

Hi

Thanks very much for the help so far. I have tried both methods you have mentioned but both seem to come up with the same error, namely a '400'. What does this mean?

Any help would be appreciated

Many Thanks

Chris

[ska67can]

You'll have to post your whole code or better yet upload your spreadsheet so we can have a better look

ska