Copy two different tables into lastrow in one table in a new sheet

[mrmcoupe]

Hei,

I´ve tweaked a code to copy/paste two different dynamic tables into one dynamic table in another sheet. However, it seems that the latter part of the code (copying the last table) overwrites the first one. I´ve used lastrow to determine the last cell with data, but it failes.

   Sub copyall()








    Dim Last_Row1 As Long, Last_Row2 As Long, Last_Row3 As Long
    Dim WB1 As Workbook, WB2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cc As Range


    Set WB1 = ThisWorkbook ' Workbook where you want to copy the data
    Set ws1 = WB1.Sheets("Sheet1") ' Change the name of your Sheet
     Set ws2 = WB1.Sheets("Sheet2") ' Change the name of your Sheet
    Set cc = WB1.Sheets("Sheet2").Range("D4:f100")
    
    
    
    cc.ClearContents


    Last_Row1 = ws1.Range("L" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    Last_Row3 = ws1.Range("F" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    
    Last_Row2 = ws2.Range("D" & Rows.Count).End(xlUp).Row + 1 ' Determine the next empty row in order to paste the data
    
  




    ws1.Range("F5:H" & Last_Row3).Copy ws2.Range("D" & Last_Row2)
    ws1.Range("L5:N" & Last_Row1).Copy ws2.Range("D" & Last_Row2)
    




End Sub

Any suggestions on how to avoid overwriting?

 

[S_Wish]

try to change this:

    Last_Row1 = ws1.Range("L" & ws1.Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    Last_Row3 = ws1.Range("F" & ws1.Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    
    Last_Row2 = ws2.Range("D" & ws2.Rows.Count).End(xlUp).Row + 1 ' Determine the next empty row in order to paste the data
    ws1.Range("F5:H" & Last_Row3).Copy ws2.Range("D" & Last_Row2)
    ws1.Range("L5:N" & Last_Row1).Copy ws2.Range("D" & Last_Row2)

 

[mrmcoupe]

Hi, thanks for very fast reply!

Unfortunately, this will give the exact same problem, and in addition, the data is copied one row lower down due to the +1 in the two last code bits.

Other suggestions?

 

[S_Wish]

where is the last row in you document? coz you delete all data from 4 to 100 row by this

 cc.ClearContents

 

[S_Wish]

forget all i told before
just insert

     Last_Row2 = ws2.Range("D" & ws2.Rows.Count).End(xlUp).Row + 1 ' Determine the next empty row in order to paste the data
    ws1.Range("F5:H" & Last_Row3).Copy ws2.Range("D" & Last_Row2)
     Last_Row2 = ws2.Range("D" & ws2.Rows.Count).End(xlUp).Row + 1 ' Determine the next empty row in order to paste the data
    ws1.Range("L5:N" & Last_Row1).Copy ws2.Range("D" & Last_Row2)

 

[mrmcoupe]

Ok, so this code clears the data from the the spreadsheet first. If I pause this, the data copied to new table (sheet 2) will accumulate, but only with the data from table two in sheet1. So, the problem is the same.

The data from the first code is overwritten

ws1.Range("F5:H" & Last_Row3).Copy ws2.Range("D" & Last_Row2) 'this is overwritten
ws1.Range("L5:N" & Last_Row1).Copy ws2.Range("D" & Last_Row2) ' by this

 

[S_Wish]

yes, you should reclaim last_row2, read post above

 

[mrmcoupe]

Hi again,

yes, this works absolutely perfectly! You saved my day! Thanks alot!

brg
Håvard