Macro to Copy Content

[WillPeters]

Hello all, new to macros here, but this one has me extra stumped.

I want to copy two cells (B21 and B23) in Sheet1 and paste the numbers into Sheet2. The catch is the figures being copied need to be pasted into the last empty column in Rows 2 and 3. The copying and pasting isn't so much an issue, but the last empty column in the row is the tricky part. Anyone able to lend me a hand? Thank you in advance!

 

[Michael M]

Try

Sub MM1()
 Dim lr As Long
 lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
 Range("B22:B23").Copy Sheets("Sheet2").Range("B" & lr + 1)
End Sub

 

[alansidman]

Option Explicit

Sub foo()
    Dim s As Variant
    Dim lc As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    lc = ws2.Cells(2, Columns.Count).End(xlToLeft).Column
    Union(ws1.Cells(21, "B"), ws1.Cells(23, "B")).Copy ws2.Cells(2, lc + 1)
    MsgBox "Action Completed"
End Sub

 

[Omar Spreadsheets]

Ctrl + Right arrow
then just left arrow

 

[WillPeters]

Amazing, thank you, alansidman! Worked beautifully

 

[alansidman]

You are welcome. Thanks for the feedback. You may want to mark it as the solution.