copy paste data to the last row of table in another sheet

[ChanL]

Hi, currently I have some data in sheet name "From Text" that I want to copy to the table in sheet "Source"
but the problem is I want to copy all the selected data to the last row of the table instead of overwriting the existing data.
i tried to google around but none of the code fit my situation.
Hope someone can help me with this!
This is the code I use to copy the data.

sub copy()
Sheets("From text').Range("A9").Select    'because I want to copy the data from row 9 to the last visible row and column
Range(selection,selection.end(xldown)).select
range(selection,selection.end(xltoright)).select
selection.copy

Sheets("Source").ListObject(1).ListRows.add     'i try to use this line to add a row to my table in this sheet but I dunno how to paste the copied data to it

End sub

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Note that
- there is no "selection" in my code as selection is rarely needed and slows code considerably.
- it is not a good idea to name a procedure (or variable) using one of the words that vba uses in its syntax (eg copy)

If this does not do what you want, please give more details.

Sub copytotable()
  With Sheets("From text")
    .Range("A9", .Range("A9").End(xlDown).End(xlToRight)).copy
  End With
  With Sheets("Source").ListObjects(1).Range
    .Cells(.Rows.Count + 1, 1).PasteSpecial xlValues
  End With
  Application.CutCopyMode = False
End Sub

 

[ChanL]

Give this a try with a copy of your workbook.

Note that
- there is no "selection" in my code as selection is rarely needed and slows code considerably.
- it is not a good idea to name a procedure (or variable) using one of the words that vba uses in its syntax (eg copy)

If this does not do what you want, please give more details.

Sub copytotable()
  With Sheets("From text")
    .Range("A9", .Range("A9").End(xlDown).End(xlToRight)).copy
  End With
  With Sheets("Source").ListObjects(1).Range
    .Cells(.Rows.Count + 1, 1).PasteSpecial xlValues
  End With
  Application.CutCopyMode = False
End Sub

Hi peter! Thanks so much for the comment and the code works perfectly for this. And thanks for your advice about the VBA name and definitely I will bare that in my mind from now on! But just an additional question, after paste the data set into the table, is it possible for me to add a line to auto add in the years and also months so that user can know which year and which months of the data is coming from.

I actually think of maybe using array and loop like this

month=array("January","February","March") 'suppose to until December, bt i just write until march for showing purposes

 

[Peter_SSs]

I have no idea what your data or table looks like or where the years and months would come from or where the years and months would be inserted so I cannot advise.
Perhaps you could post some small sample dummy data XL2BB and explain carefully in relation to that sample.

 

[ChanL]

I have no idea what your data or table looks like or where the years and months would come from or where the years and months would be inserted so I cannot advise.
Perhaps you could post some small sample dummy data XL2BB and explain carefully in relation to that sample

Hi Peter. I have uploaded a mini sheet for your reference.
So basically, what I want is that after user copy the data to the last row of the table, the VBA code will automatically fill in the respective years and date at their columns.
I thinking of maybe can try to use array or maybe an inputbox VBA will be better?
Need your advice.
Thanks!

Book1
	A	B	C	D
1	Branch	Amount	Year	Month
2	Botanic Garden	100
3	Kuala Lumpur	34
4	Bukit Nanas	22
5	Setapak	56
6	Ipoh	33
7	Penang	90
Sheet1

 

[Peter_SSs]

fill in the respective years and date at their columns.

I assume you mean respective year and month? But I still don't know what year would go in what row and same for month.

 

[ChanL]

I assume you mean respective year and month? But I still don't know what year would go in what row and same for month.

what i mean is , for example, if the data copied is data from year 2021 and month June, then the rows with the data need to fill in with 2021 and June for column C and column D. As everytime when data was copied and add into the table, I want user to label which year and month the data is copied

 

[Peter_SSs]

year and month the data is copied

OK, thanks, that is what I needed to know.

Your table has 4 columns so I assume that copy/paste part will be pasting just 2 columns. In that case, give this a try

Sub copytotable_v2()
  Dim rLO As Range
  Dim fr As Long, fc As Long
  
  With Sheets("From text")
    .Range("A9", .Range("A9").End(xlDown).End(xlToRight)).copy
  End With
  With Sheets("Source")
    Set rLO = .ListObjects(1).Range
    fr = rLO.Row + rLO.Rows.Count
    fc = rLO.Column
    .Cells(fr, fc).PasteSpecial xlValues
    .Cells(fr, fc + 2).Resize(.Cells(fr - 1, fc).End(xlDown).Row - fr + 1).Resize(, 2).Value = Array(Year(Date), Month(Date))
  End With
  Application.CutCopyMode = False
End Sub