Creating variables inside ranges-CodePudding

I tried using a variable inside a range with the purpous of only doing the necessary rows instead of doing a pre-determined range.

I don't know why it won't let me write it like this

If anyone could help me with this issue, it would be greatly appreciated!

Row = Sheets("Checklist").Range("V2").End(xlDown).Row
Parts = 4

For PN = 2 To Row  

    Sheets("Checklist").Range(Cells(PN, 22), Cells(PN, 24)).Copy

    Sheets("Pipeline").Cells(Parts, 4).PasteSpecial

    Parts = Parts   1

Next PN

Application.CutCopyMode = False

End Sub

CodePudding user response：

It would be easier if we knew what you are trying to copy and past, because you copied a range and paste it to a cell, is that a merged range?

If that is the case, you dont need to use .Copy, you could do something like that insted:

my_Row = Sheets("Checklist").Range("V2").End(xlDown).Row
Parts = 4

For PN = 2 To my_Row 
    Sheets("Pipeline").Cells(Parts, 4) = Sheets("Checklist").Cells(PN, 22)
    Parts = Parts   1
Next PN

End Sub

CodePudding user response：

The issue you're seeing is probably on this line:

Sheets("Checklist").Range(Cells(PN, 22), Cells(PN, 24)).Copy

Your Cells(PN, 22) aren't qualified to a specific Sheet.

However, you appear to be creating a loop for no reason, why not copy across the whole block in one go?

Sheets("Pipeline").Cells(Parts, destinationRow).Resize(3, lastRow - firstRow   1).Value = _
    Sheets("Checklist").Range(Sheets("Checklist").Cells(firstRow, 22), Sheets("Checklist").Cells(lastRow, 24)).Value

To tidy it all up a bit, the following should work:

'Declare variables
Dim firstRow As Long, lastRow As Long, destinationRow As Long

'Set variables
firstRow = 2
lastRow = Sheets("Checklist").Range("V2").End(xlDown).Row
destinationRow = 4
destinationColumn = 4

'Copy data
With Sheets("Checklist")
    Sheets("Pipeline").Cells(destinationColumn, destinationRow).Resize(3, lastRow - firstRow   1).Value = _
        .Range(.Cells(firstRow, 22), .Cells(lastRow, 24)).Value
End With