links to table corrupting when new data pasted in

[ajm]

I download a report from our ERP which is then pasted into a file. The destination worksheet ("T1 Download") is set up as a table (Table1) so that the range expands appropriately as the records increase each week. On another tab in this workbook ("CalcSheet"), there is a second table (Table2) that links to the original. Formulas used in Table 2 are very simply copying the contents of the same cell in Table1. so, the first cell in Table 2 (range A6) contains the formula "= 'T1 Download'!A6". The worksheet was set up this way to reorder the downloaded data by column. so, each row in Table2 corresponds to the same row in Table1.

Problem i am having lately is that when the new data is pasted into Table1, the formulas on the second tab ("CalcSheet") seem to drop a few rows so that i end up with fewer rows of data on this second tab.

Has anyone else experienced anything like this? Has anyone got a suggestion(s) on how to do the column reorder task that "CalcSheet" performs?

 

[ajm]

I am trying to find out whether this thread relates to the one below and whether they are mutually exclusive or not.
copy first three columns of table then rest of it

Alex, both questions relate to the same model but deal with different stages of the solution.

 

[Alex Blakenburg]

Alex, both questions relate to the same model but deal with different stages of the solution.

Thanks for clarifying, I have put some code on the other thread for you to try.

 

[ajm]

further to this issue, i also posted a query regarding copying the first row of a table down the entire range of the table. found several posts, but the easiest came from JoeMO. Thread to my question (including a link to Joe's answer on yet another thread) is here:

copy first row of table

to filldown a range on a sheet, the macro recorder proposes: Range("A6:X6").Select Selection.AutoFill Destination:=Range("CalcPPR"), Type:=xlFillDefault where row 6 on the sheet is the first row of data in the named range "CalcPPR" what is the syntax for doing the same thing with a Table?

www.mrexcel.com

after using HSTACK and CHOOSECOLS a few times, it didn't exactly fit my purpose. have used it elsewhere already though so still stoked with it.

 

[Alex Blakenburg]

Thanks for the follow up. Glad you found something that works for you.