Copy data to the first row of table

ChanL

Board Regular
Joined
Apr 8, 2021
Messages
65
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJ
1Order IDShip DateOrder DateShip ModeCustomer IDCustomer NameDestinationContact NumberStatusSegment
2
3CA-2014-10589311/18/201411/11/2014Standard ClassPK-19075Pete KrizNew York123456On the WayConsumer
4CA-2014-1671645/15/20145/13/2014Second ClassAG-10270Alejandro GroveWashington123456FailedConsumer
5CA-2014-1433369/1/20148/27/2014Second ClassZD-21925Zuschuss DonatelliNew Zealand123456DeliveredConsumer
6CA-2014-1433369/1/20148/27/2014Second ClassZD-21925Zuschuss DonatelliNew York123456On the WayConsumer
7CA-2014-1433369/1/20148/27/2014Second ClassZD-21925Zuschuss DonatelliWashington123456DeliveredConsumer
8CA-2016-13733012/13/201612/9/2016Standard ClassKB-16585Ken BlackJapan123456On the WayCorporate
9CA-2016-13733012/13/201612/9/2016Standard ClassKB-16585Ken BlackNew York123456DeliveredCorporate
10US-2017-1569097/18/20177/16/2017Second ClassSF-20065Sandra FlanaganWashington123456On the WayConsumer
11CA-2015-1063209/30/20159/25/2015Standard ClassEB-13870Emily BurnsNew Zealand123456On the WayConsumer
12CA-2016-1217551/20/20161/16/2016Second ClassEH-13945Eric HoffmannNew York123456FailedConsumer
13CA-2016-1217551/20/20161/16/2016Second ClassEH-13945Eric HoffmannWashington123456On the WayConsumer
14US-2015-1506309/21/20159/17/2015Standard ClassTB-21520Tracy BlumsteinNew Zealand123456DeliveredConsumer
15US-2015-1506309/21/20159/17/2015Standard ClassTB-21520Tracy BlumsteinNew York123456On the WayConsumer
16US-2015-1506309/21/20159/17/2015Standard ClassTB-21520Tracy BlumsteinWashington123456DeliveredConsumer
Sheet1


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

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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top