Copy Table from last filled row in one sheet to last filled row in another sheet

[waltermelonwong]

Problem:
As the number of rows in my table in the Sheet("Table") changes according to the different tenor(number of years) and frequency(Annual, Semi, Quarterly), I need a function for my button "Copy" in my Sheet("Master") to copy the table in the Sheet("Table") till the last filled row (as the number of row changes) and paste it to the next empty row on a different Sheet("Print"). (In actual fact there may be other filled rows before pasting).

Currently the Table in Sheet("Table") is 5 Columns from A:E, number of rows varies.
While the number of rows from Sheet("Print") varies too.

Thank you !

 

[oldbrewer]

you can have a helper cell that counts the number of filled rows, if it is say 17 add 1 = 18 so cells(18,1) is where you paste new data

 

[waltermelonwong]

Hi, is there a VBA code for that ? Sorry I'm a VBA newbie and its really tough scavenging and piecing codes online to make it work. Must have tried like tens of different combinations.

 

[JPARKHURST]

Try this and see how it works for you.

On the button call, CopySheet(Sheet("Table"),Sheet("Print)) - OR - set variables to your sheet names, and push those over, either way works. The last entry

sjd105 is your source, swd105 destination

!! This assumes you will have all rows in column A filled (if there are blanks, we can look at another way).


Public Sub CopySheet(sjd105 As Worksheet, swd105 As Worksheet)

Dim lr105 As Long
Dim lr105b As Long
Dim rJd1 As Range
Dim rJd2 As Range

lr105a = sjd105.Cells(Rows.Count, "A").End(xlUp).Row
lr105b = swd105.Cells(Rows.Count,"A").End(xlUp).Row
Set rJd1 = sjd105.Range("A2:E" & lr105a)
Set rJd2 = swd105.Range("A" & lr105b)
rJd2.Value = rJd1.Value

swd105.Activate
End Sub

 

[My Aswer Is This]

Assuming your table on sheet named "Table" is named "Table1"
Try this:

This assumes we are looking to paste the table into sheet named "Print"

First Table column is pasted into column 1 of sheet named Print

Sub Copy_Table()
'Modified 6/11/18 12:15 PM EDT
Dim Lastrow As Long
Lastrow = Sheets("Print").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Table").ListObjects("Table1").Range.Copy Sheets("Print").Range("A" & Lastrow)
End Sub