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

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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.

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,605
Office Version
  1. 365
Platform
  1. Windows
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.

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

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
month=array("January","February","March") 'suppose to until December, bt i just write until march for showing purposes
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,605
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
ABCD
1BranchAmount Year Month
2Botanic Garden100
3Kuala Lumpur34
4Bukit Nanas22
5Setapak56
6Ipoh33
7Penang90
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,605
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,605
Office Version
  1. 365
Platform
  1. Windows
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

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

Forum statistics

Threads
1,141,303
Messages
5,705,590
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top