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

Mr_Ragweed2

Board Regular
Joined
Nov 11, 2022
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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").

VBA Code:
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Update: I've tried this but still no luck:
VBA Code:
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:

VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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:
VBA Code:
Dim ThisFinal As Long
ThisFinal = OSumWS.Cells(Rows.Count, 2).End(xlUp).Row
 
Upvote 0
I'm getting the runtime 1004 error

VBA Code:
 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:
VBA Code:
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.
 
Upvote 0
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.

VBA Code:
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?
 
Upvote 0
I did it!!! It is not pretty at all but it works. There to be a better way though.

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top