Excel VBA Paste in Last Row with Data

[bored622]

Hello everyone,

I'm trying to create a macro that copies cells from A1:C3 in one workbook and pastes them in the last row with the text Period Three in another workbook in column C. Every time a new last row has period 3 that data will only paste in that new last row with Period Three.

This is workbook 1 sheet 1

1	2	3
4	5	6
7	8	9

This is workbook 2 sheet 1

date	key	Period One
date	key	Period Two
date	key	Period Three	1	2	3	4	5	6	7	8	9

Thanks for any help.

 

[DanteAmor]

Hello @bored622, I hope you are well

Try this:

Sub Paste_in_last_row()
  Dim f As Range
  
  With Workbooks("Workbook 1.xlsx").Sheets(1)
    Set f = Workbooks("Workbook 2.xlsx").Sheets(1).Range("C:C").Find("Period Three", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not f Is Nothing Then
      f.Offset(, 1).Resize(1, 3).Value = .[A1].Resize(1, 3).Value
      f.Offset(, 4).Resize(1, 3).Value = .[A2].Resize(1, 3).Value
      f.Offset(, 7).Resize(1, 3).Value = .[A3].Resize(1, 3).Value
    End If
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​