resize for copy

[Danny54]

how do i adjust the following code to change the copy and paste from Range("A" & i) to ("E" & i ).

i get a object doesnt support the property when i change A to E.

I tried to do offset(0, 4) and get a "You cannot past this here because the Copy area and paste area are not the same size.

Thanks

Dim j As Long
For j = 4 To lastrow
If Cells(j, 10).Value <> " " Then
i = i + 1
Rows(j).Copy Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A" & i)
End If
Next

 

[Fluff]

If you copy an entire row, then you have to copy it to col A, otherwise it's trying to put some of the cells off the edge of the sheet.

What range do you need to copy?

 

[Paul Ked]

It's because you are trying to copy the whole row. Change the Rows(j) to the range you want to copy.

 

[Danny54]

i only need to copy a-j but paste it into e-n

 

[Fluff]

In that case try

Range("A" & j).Resize(, 10).Copy ThisWorkbook.Worksheets("Sheet1").Range("E" & i)

 

[Danny54]

perfect and I learnt something too. It didn't even enter my brain that a copy took the whole row and when i was trying to paste it i would push it off the end. (to the right)

Thanks so much.

 

[Fluff]

Glad we could help & thanks for the feedback