1

I have a table located on Sheet1 called Table2, I just need a way to paste it onto Sheet2 in a certain cell to prepare it for a CSV file. Is there a formula I can use in a cell to display my table in a different sheet?

Table2 dynamically changes from week to week, so I need a formula that'll copy and paste the whole table onto Sheet2.

I've only found how to do a structured table reference, but have not found how to copy and paste the whole table.

MSauce
  • 123
  • 2
  • 13
  • Are you looking for a formula, strictly based in `Excel` by itself, or `VBA`, or maybe `C#`? – gravity May 22 '18 at 14:45
  • I'd prefer a formula so that it dynamically adjusts, I dont have to run a line of vba code, and not worry about formatting if I copied and pasted it with vba. I just need the raw data, something simple. – MSauce May 22 '18 at 14:48

2 Answers2

0

In VBA you can use this one line of code in a new module:

Sub CopyTable1ToSheet2A1()
    Sheet1.ListObjects("Table1").Range.Copy Destination:=Sheet2.Range("A1")        
End Sub
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • 1
    Is it possible to accomplish this with a formula? I'd like to avoid running macros on the second sheet and remove the formatting on the table. – MSauce May 22 '18 at 15:01
0

tl;dr; You can insert the table from a connection.

  1. Create your source table if not there already.

enter image description here

  1. Change name if you want, mine is src_table:

enter image description here

  1. Goto "Data -> Get External Data -> Existing Connections"

enter image description here

  1. Goto "Tables" tab and select your table:

enter image description here

  1. In the "Import data" dialog, select how as "Table" and where as wherever you want. In my case I put it in my existing Sheet2 cell A4.

enter image description here

  1. Press Ok

enter image description here

  1. Now if you update the original table in Sheet1, then come back to Sheet2, right click and "Refresh", it'll fetch teh latest data.

enter image description here

Kashyap
  • 15,354
  • 13
  • 64
  • 103