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

mrmcoupe

New Member
Joined
Jan 22, 2017
Messages
17
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.

Code:
   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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

S_Wish

Board Regular
Joined
Jan 4, 2017
Messages
216
try to change this:

Rich (BB code):
    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)
 
Last edited:

mrmcoupe

New Member
Joined
Jan 22, 2017
Messages
17
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

Board Regular
Joined
Jan 4, 2017
Messages
216
where is the last row in you document? coz you delete all data from 4 to 100 row by this
Code:
 cc.ClearContents
 

S_Wish

Board Regular
Joined
Jan 4, 2017
Messages
216

ADVERTISEMENT

forget all i told before
just insert
Code:
     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

New Member
Joined
Jan 22, 2017
Messages
17
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

Code:
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
 

mrmcoupe

New Member
Joined
Jan 22, 2017
Messages
17
Hi again,

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

brg
Håvard
 

Watch MrExcel Video

Forum statistics

Threads
1,109,131
Messages
5,527,020
Members
409,736
Latest member
maanbunty

This Week's Hot Topics

Top