alansidman Mountain Moderator

Hey Alan,

I recently saw your comment on copy and pasting rows based off of specific criteria back in 2018 (sorry long time ago). For reference:

I'm very new to Macros and want to write one to copy and paste columns DB:DJ and EM:EX on sheet1 (Origin) to Columns AM:BG on sheet 2 (Destination) as long as the value in column L on Sheet1 matches Column M on sheet 2. Basically if the cell value matches both sheets the row from sheet 1 that matches the cell will copy the columns above to sheet 2 in the matching cell columns above.

Would this be something you could help me out with? I was trying to take the columns based off of how far away they were from Column L on Sheet1 and Column M on sheets 2.

So far I have below: I'm probably way off so any help is much appreciated!

Sub ABC()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Dim ir As Long, ir2 As Long
Dim i As Long
ir = s1.Range("L" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To ir
ir2 = s2.Range("M", Rows.Count).End(xlUp).Row
If s1.Range("L" & i) = s2.Range("M" & i) Then
s1.Range("L" & i).Rows.Count.Copy = (94, 95, 96, 97, 98, 99, 100, 101, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143))
s2.Range("M" & ir2 + 1).Entire.Row(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47).PasteSpecial xlPasteValues
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Complete"

Please feel free to message me with any questions.

Thank you!

Hi Alan, I'm not familiar with everything about this forum's interface. I see in this thread you tagged it as a cross-post. But I do not see a link to the cross-post anywhere. What am I missing?

Love your horse