Copy data to the first row of table

[ChanL]

I have created the following VBA for user to copy data from Sheet "fromtext" to sheet"report" which contain a table that I created (the table is created with ctrl + T , not ordinary range table.

There will always be time users will need to duplicate the workbook to compile data for a brand new year.
So for this, after a new duplicated workbook is created, they will clear the content in the table and start run the VBA again.
But the problem is, everytime after the table cleared, the data always pasted at the last row of table which automatically make the data cannot fit into the table nicely.
So , I thinking of modify my current code, but I need advice.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Order ID	Ship Date	Order Date	Ship Mode	Customer ID	Customer Name	Destination	Contact Number	Status	Segment
2
3	CA-2014-105893	11/18/2014	11/11/2014	Standard Class	PK-19075	Pete Kriz	New York	123456	On the Way	Consumer
4	CA-2014-167164	5/15/2014	5/13/2014	Second Class	AG-10270	Alejandro Grove	Washington	123456	Failed	Consumer
5	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	New Zealand	123456	Delivered	Consumer
6	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	New York	123456	On the Way	Consumer
7	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	Washington	123456	Delivered	Consumer
8	CA-2016-137330	12/13/2016	12/9/2016	Standard Class	KB-16585	Ken Black	Japan	123456	On the Way	Corporate
9	CA-2016-137330	12/13/2016	12/9/2016	Standard Class	KB-16585	Ken Black	New York	123456	Delivered	Corporate
10	US-2017-156909	7/18/2017	7/16/2017	Second Class	SF-20065	Sandra Flanagan	Washington	123456	On the Way	Consumer
11	CA-2015-106320	9/30/2015	9/25/2015	Standard Class	EB-13870	Emily Burns	New Zealand	123456	On the Way	Consumer
12	CA-2016-121755	1/20/2016	1/16/2016	Second Class	EH-13945	Eric Hoffmann	New York	123456	Failed	Consumer
13	CA-2016-121755	1/20/2016	1/16/2016	Second Class	EH-13945	Eric Hoffmann	Washington	123456	On the Way	Consumer
14	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	New Zealand	123456	Delivered	Consumer
15	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	New York	123456	On the Way	Consumer
16	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	Washington	123456	Delivered	Consumer
Sheet1

With Sheets ("fromtext")
  . range("A9",.range("A9").end(xlDown).End(xltoright).copy
end with

With sheets("report").listobjects(1).range
   .cells(.rows.count+1,1).pastespecial xlvalues
end with

application.cutcopymode=false

 

[johnnyL]

Can you upload the workbook?

There are numerous problems with the line of code:

. range("A9",.range("A9").end(xlDown).End(xltoright).copy

 

[ChanL]

Can you upload the workbook?

There are numerous problems with the line of code:

. range("A9",.range("A9").end(xlDown).End(xltoright).copy
[/QUOTE]

 

[Peter_SSs]

You have some typos in that posted code (always better to copy/paste your actual working code rather than re-typing it in the forum) but try this with a copy of your workbook.

Dim NextRow As Long

With Sheets("fromtext")
  .Range("A9", .Range("A9").End(xlDown).End(xlToRight)).Copy
End With

With Sheets("report").ListObjects(1).Range
  If .SpecialCells(xlConstants).Count = .Columns.Count Then
    NextRow = 2
  Else
    NextRow = .Rows.Count + 1
  End If
  .Cells(NextRow, 1).PasteSpecial xlValues
End With

Application.CutCopyMode = False

 

[ChanL]

You have some typos in that posted code (always better to copy/paste your actual working code rather than re-typing it in the forum) but try this with a copy of your workbook.

Dim NextRow As Long

With Sheets("fromtext")
  .Range("A9", .Range("A9").End(xlDown).End(xlToRight)).Copy
End With

With Sheets("report").ListObjects(1).Range
  If .SpecialCells(xlConstants).Count = .Columns.Count Then
    NextRow = 2
  Else
    NextRow = .Rows.Count + 1
  End If
  .Cells(NextRow, 1).PasteSpecial xlValues
End With

Application.CutCopyMode = False

thanks for the advise, but the actual code is at my working laptop and there is a restriction for staff to share files to external sources. So, it leave me no choice but to re-type everything. I will try this code first

 

[ChanL]

Hi, do you mind to share how to upload workbook to this platform? As xl2bb extension only can upload range of table

 

[Peter_SSs]

Hi, do you mind to share how to upload workbook to this platform?

This forum does not allow upload of actual workbooks, that is why we have XL2BB

As xl2bb extension only can upload range of table

We generally find that sufficient & I note in another thread you have it working.

Did you try my code yet?

 

[ChanL]

This forum does not allow upload of actual workbooks, that is why we have XL2BB


We generally find that sufficient & I note in another thread you have it working.

Did you try my code yet?

This forum does not allow upload of actual workbooks, that is why we have XL2BB


We generally find that sufficient & I note in another thread you have it working.

Did you try my code yet?

i tried your code. It work perfectly on my dummy data , but it does not work on my actual data that is in my office laptop. I wonder if the code that I used before this copypaste code got any effect on it?

 

[johnnyL]

This forum does not allow upload of actual workbooks, that is why we have XL2BB

@Peter_SSs is correct there. But you can google 'Free file upload' and post the link back here. I recently found gofile.io
it offers unlimitted access as far as upload file size

 

[ChanL]

thanks for reply. @Peter_SSs code work perfectly on my dummy data that I created to test the code at my personal laptop. But when I use the same code for my actual data, it doesn't work. I will put in the code that I used before the copy paste code here . Hope to get some advice from both of you.

@Peter_SSs is correct there. But you can google 'Free file upload' and post the link back here. I recently found gofile.io
it offers unlimitted access as far as upload file size