copy nonadjacent cells from one ws to next available row on another ws

[Mr_Ragweed2]

Hello and thanks.
I have four cells on a Sheet1 ("T39", "T47", "T57", "N61:N62") that i would like to copy and paste to the next available row on Sheet2 in range ("N:Q").

ElseIf Cells(i, 3).Value = "" Then
      
    Sheets("Sheet 1").Range("T39", "T47", "T57", "N61:N62").Copy
    Sheets("Sheet 2").Cells(ThisFinal + 1, 14).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If

It is not working however. (earlier in code "ThisFinal" is written as a FinalRow Statement that works in other parts of the code.)

Thought 1: I'm a vba novice and the fix is easy.
Thought 2: The ElseIf statement is causing problems. I have an IF statement before that executes code based on any cell in column C, rows 19-31 that have content Then do x. It runs fine. What i'm trying to accomplish with my elseif statement is that once the first loop finds a blank in column C, then it copies the four cells i referenced in the beginning.
Thought 3: Thoughts 1 & 2 can be true at the same time

Thanks for your interest and time.

 

[Mr_Ragweed2]

Update: I've tried this but still no luck:

Dim rng As Range
     With Sheets("Dekalb Seed Order Form").Activate
        Set rng = Union(.Range("T39"), .Range("T47"), .Range("T57"), .Range("N61:N62"))
        rng.Copy
        Sheets("Order Summary").Cells(ThisFinal + 1, 14).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
     End With

Also realized my first code on this thread was wrong so i changed it to this with no avail:

Sheets("Dekalb Seed Order Form").Range("T39,T47,T57,N61:N62").Select
    Selection.Copy
    Sheets("Order Summary").Cells(ThisFinal + 1, 14).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

I am really struggling with this. Thanks to any who can point me in the right direction.

 

[Mr_Ragweed2]

I really really need some help. I am trying very hard to do this myself but i am a VBA novice (at best).
no emojis for pulling your hair out, but i would put one here
Been searching this forum and the web and the results make it seem easy but i cannot pull it off. My latest attempt is below:

Dim copyRange As Range
     Dim cel As Range
     Dim pasteRange As Range
     Dim ecolumn As Long
     
     Set copyRange = Sheets("Dekalb Seed Order Form").Range("T39,T47,T57,N61:N62")
     Set pasteRange = Sheets("Order Summary").Cells(ThisFinal + 1, 14)
     
     For Each cel In copyRange
       cel.Copy
        
        ecolumn = Sheets("Order Summary").Cells(2, Columns.Count).End(xlLeft).Offeset(0, 14).Column
        pasteRange.Cells(1, ecolumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
        
        Next
        Application.CutCopyMode = False
        [CODE=vba]

Earlier in Macro i have:

Dim ThisFinal As Long
ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row

 

[Mr_Ragweed2]

I'm getting the runtime 1004 error

 Dim copyRange As Range
     Dim cel As Range
     Dim pasteRange As Range
     Dim FinalColumn As Long
     
     Set copyRange = Sheets("Dekalb Seed Order Form").Range("T39,T47,T57,N61:N62")
     
     For Each cel In copyRange
       cel.Copy
         FinalColumn = Sheets("Order Summary").Cells(2, Columns.Count).End(xlLeft).Offset(1, 14).Column
        pasteRange.Cells(2, FinalColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
       
        Next
        Application.CutCopyMode = False
    
    End If

The error appears on this line:

FinalColumn = Sheets("Order Summary").Cells(2, Columns.Count).End(xlLeft).Offset(1, 14).Column

It says FinalColumn = 0.
My data runs through columns B:T. Important to note that columns A, D, F, H, J, L, N:Q, & R:S are blank. I am trying to paste in columns N:Q on the next available row. I put data in column A to see if having the first column being empty was the issue but i got the exact same result.

I really appreciate any help i can get on this. I know me replying to my own thread is not the best as far as this showing as unanswered. Sorry.

 

[Mr_Ragweed2]

Making progress.... I can now get the last cell of my range to copy and paste to the right location. I just cannot get the entire range to paste.

Dim copyRange As Range
     Dim cel As Range
     Dim pasteRange As Range
     Dim FinalColumn As Long
     
     Set copyRange = Sheets("Dekalb Seed Order Form").Range("T39,T47,T57,N61")
     Set pasteRange = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     
     For Each cel In copyRange
       cel.Copy
         FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(0, -3).Column
        pasteRange.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        Next
        Application.CutCopyMode = False
    
    End If

I had to adjust my offset. I edit my copy range to the cell that had data. Any ideas?

 

[Mr_Ragweed2]

I did it!!! It is not pretty at all but it works. There to be a better way though.

Dim copyRange1 As Range
     Dim copyRange2 As Range
     Dim copyRange3 As Range
     Dim copyRange4 As Range
     
     Dim cel As Range
     Dim pasteRange1 As Range
     Dim pasteRange2 As Range
     Dim pasteRange3 As Range
     Dim pasteRange4 As Range
     
     Dim FinalColumn As Long
     
     Set copyRange1 = Sheets("Dekalb Seed Order Form").Range("T39")
     Set copyRange2 = Sheets("Dekalb Seed Order Form").Range("T47")
     Set copyRange3 = Sheets("Dekalb Seed Order Form").Range("T57")
     Set copyRange4 = Sheets("Dekalb Seed Order Form").Range("N61")
     
     Set pasteRange1 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange2 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange3 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
     Set pasteRange4 = Sheets("Order Summary").Cells(ThisFinal + 1, 1)
 
     For Each cel In copyRange1
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(0, -6).Column
        pasteRange1.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Next
     
     For Each cel In copyRange2
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(0, -5).Column
        pasteRange2.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
      For Each cel In copyRange3
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(0, -4).Column
        pasteRange3.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
       For Each cel In copyRange4
       cel.Copy
        FinalColumn = Sheets("Order Summary").Cells(1, Columns.Count).End(xlToLeft).Offset(0, -3).Column
        pasteRange4.Cells(1, FinalColumn).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Next
      
        Application.CutCopyMode = False
    
    End If