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

 

[ChanL]

@johnnyL @Peter_SSs

Here are the code that I used before the copy paste code.

the first one here is to import data from text file to sheet call "from text"

dim x as double, txt as string, filename as string

filename=Sheets("Main').Cells(4,2).value +"\"   + "reportfeb.txt"

open filename for input as #1

do while not EOF (1)
line input #1, txt
thisworkbook.sheets("fromtext").cells(1,1).offset(x,0)=txt
x=x+1
loop
close#1

the second code here is what I use to format the text file

sheets("fromtext").select
sheets("fromtext").columns("A:A").select
selection.texttocolumns destination:=range("A1"), datatype:=xlfixedwidth, _
       fieldinfo:=array((array0,1),array(5,1),array(23,1),array(42,1),array(54,1), _
       array(69,1), array(79,1), array(97,1), array(109,1), array(123,1)), trailingminusnumbers:= true

sheets("fromtext").range("A:A").Autofilter Field:=1, criteria1:=Array("ATM", "CODE", "MYM", "OOBC", "TOTAL", " "),operator:=xlfiltervalues

dim lastrow as long, rng as range
lastrow= sheets("fromtext").cells.find("*",searchorder:=xlbyrows, searchdirection:=xlprevious).row
set rng=rows("9:" & lastrow)
rng.delete shift:=xlup
selection.autofilter

 

[ChanL]

Hi @Peter_SSs and @johnnyL

I got the solution alrdy! Thanks so much for both of your help.

and Peter's code now work perfectly as well at my actual data.

Is my bad because I didn't change a small part of the code which cause the code to fail!

But anyway , both of you are life savers especially to VBA beginner like me. I learnt another new thing today

 

[Peter_SSs]

code now work perfectly as well at my actual data.

Great news! Glad you got it resolved. Thanks for letting us know.