0

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

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

I receive a sheet with an order for parts. The amount of items that need to be brought over to a data base vary depending on the order.

The layout is somewhat like this:

V W X
1 Parts Version Yearly volume
2 123 A 100
3 456 B 200
4 789 C 300

I tried copying line by line creating some sort of loop, in which one of you guys brought up that it's not necessary and you were right.

However, I still need to copy the whole table and bring it to the data base sheet.

For that I used the following code:

LastRow = Sheets("Checklist").Range("V2").End(xlDown).row

If Sheets("Pipeline").Range("D4") = "" Then
    Parts = 4
    GoTo CopyPaste
    
ElseIf Sheets("Pipeline").Range("D5") = "" Then
    Parts = 5
    GoTo CopyPaste

Else
    Parts = Sheets("Pipeline").Range("D4").End(xlDown).row + 1

End If

CopyPaste:
    
    ' This is the line of code that I'm having problems, it keeps giving me an 1004 error'
    ->Sheets("Checklist").Range(Cells(2, 22), Cells(LastRow, 24)).Copy
    Sheets("Pipeline").Cells(Parts, 4).PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub
Community
  • 1
  • 1
Ek.murg
  • 1
  • 1
  • Can you clean up the formatting please? Difficult to see what your doing. I wouldn't assign "Row = " as a variable, as its a function in vba so just asking for trouble. Try "row1 = " Also looks like you need to correct your use of " within the copy line. probably looking at something like Cells("&PN&",22):Cells("&PN&",24)) – Amiga500 Nov 22 '21 at 14:12
  • Are you getting runtime error 1004: Subscript out of range? – Warcupine Nov 22 '21 at 14:16
  • **1.** What error are you getting? **2.** It is advisable to [fully qualify](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) your cells object else you will get an error if Sheets("Checklist") is not active. – Siddharth Rout Nov 22 '21 at 14:39
  • I would say it's an "activesheet" issue on the .cells(x,y) lines. Is there any need to copy? Just say along these lines `Sheets("Pipeline").Cells(Parts, 4).value = Sheets("Checklist").Range(Cells(PN, 22), Cells(PN, 24)).value` – Nathan_Sav Nov 22 '21 at 14:45
  • See if I was able to clarify my intentions! – Ek.murg Nov 22 '21 at 17:01

2 Answers2

0

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
Felipe Mezzarana
  • 87
  • 7
0

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
CLR
  • 11,284
  • 1
  • 11
  • 29