VBA Copying a Range and pasting to another worksheet

Question

I'm trying to copy a range from a cell to the end of another cell in the same row. I have the following code in an if loop.

Sheet2.Range(Cells(i, 2), Cells(i, c)).Copy
Sheet3.Cells(i, 2).Paste

Sheet2 and Sheet 3 are structured in the same way thus I'm using the same variables for the row.

On top of that, how do I paste cells to the next empty cell of that particular row of the loop?

Are you having problems copying and pasting? If so what error at you getting at what line. — Mr ML
It will run into this error "Method 'Range' of object '_Worksheet' failed" — Gary Tang

Nikolaos Polygenis Nikolaos Polygenis · Accepted Answer · 2018-12-19T10:11:12

Please replace with the below:

Dim rn As Range, rn1 As Range, sh As Worksheet, sh1 As Worksheet


Set sh = Sheet2
Set sh1 = Sheet3

With sh

Set rn = .Range(.Cells(i, 2), .Cells(i, c))
Set rn1 = sh1.Cells(i, 2)


rn.Copy rn1

End With

if c it is not a variable please replace with "c"

VBA Copying a Range and pasting to another worksheet

2 Answers