resize for copy

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
65
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 4, 2015
Messages
442
It's because you are trying to copy the whole row. Change the Rows(j) to the range you want to copy.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
In that case try
Code:
Range("A" & j).Resize(, 10).Copy ThisWorkbook.Worksheets("Sheet1").Range("E" & i)
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
65
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,082,612
Messages
5,366,610
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top