Consulting

Results 1 to 3 of 3

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

  1. #1

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

    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.


    1. Workfile from which to copy last row shall ideally remain closed
    2. 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
    3. The last row will change each month, it means that each month a new row will be added
    4. Data set starts in row 5 (before in the first rows is some text)
    5. 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
    Attached Files Attached Files

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    618
    Location
    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.
    Attached Files Attached Files

  3. #3
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •