VBA copy last row of a sheet of a workbook to last row of sheet in another workbook

[megaheinz]

Dears
As beginner of VBA I'd like your help to have a macro to copy the last row of a sheet of a workbook to the last row of a sheet in another workbook.
I searched here and found similar solved requests but not exactly the one fitting fully to my request.

I have enclosed an example workbook (for simplification reasons I put the two sheets in the same workbook): data to be copied in the month of September are marked in yellow: in the source file & source sheet it is cell A14 to G14, in the destination file & sheet it shall be copied to cell D14 to J14.
In October it should be then cell A15 to G15 to be copied to cell D15 to J15.



Workfile from which to copy last row shall ideally remain closed
Workfile name from which to copy will not change, also not the name of the worksheet in this file (so in attached example it will be "Testmacro.xlsm" for the Source file and the sheetname "Sheetwithdatatocopy"). Same for the destination file: no change in file name as well sheet name
The last row will change each month, it means that each month a new row will be added
Data set starts in row 5 (before in the first rows is some text)
The last row of this worksheet shall be copied into the last row of another workfile and corresponding sheet (in this example the sheetname is "DestinationSheet")


I appreciate any support and I hope that my description is clear.
Thank you in advance
Best regards
Megaheinz

[Logit]

The following works here. You most like will need to change the name of the destination workbook in the macro code. For my purposes of testing here, I named the destination workbook TempWB and it was
placed on my desktop. You will need to place your destination workbook on the desktop as well or change the macro code to point to the location in your situation.


Option Explicit

Sub copy_wb()
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Mysheet As Worksheet
Dim FromlastRow As Long
Dim ToLastRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set DestWB = Workbooks.Open(Environ("USERPROFILE") & "\OneDrive\Desktop\TempWB")
Set Mysheet = ThisWorkbook.Worksheets("Sheetwithdatatocopy")
Set DestSh = DestWB.Worksheets("Sheet1")
FromlastRow = Mysheet.Cells(Rows.Count, 7).End(xlUp).Row '- 1
ToLastRow = DestSh.Cells(Rows.Count, 1).End(xlUp).Row + 1
With Mysheet
'.Visible = True
.Activate
.Range("A" & FromlastRow).EntireRow.Copy _
Destination:=DestSh.Range("A" & ToLastRow)
'.Visible = xlVeryHidden
End With
Application.CutCopyMode = False
DestWB.Close savechanges:=True
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub





The above code is also presuming you will always have data in column G. It is column G that the macro is searching for the last row used.

I just noticed you have additional data in the destination workbook below the table to which you are pasting. At the moment I am not certain how
you would acommodate for that additional data. You might be able to add to the copy/paste macro additional code that would temporarily cut that
data, then paste it back below the table after adding your new row in the table. ???? Or perhaps someone else can present a more efficient
solution.

[megaheinz]

Dear Logit, thank you very much for the quick reply.
The macro code works as such but it copies in my case the data to row 33 in the destination workbook and corresponding sheet and further and not row 14 (starting with D14).
I can shift normally the data put below the first table where the data should be copied.

Furthermore: is it possible to keep the SOURCE workbook closed and to insert the macro in the opened DESTINATION workbook?