 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
How would one copy tables containing numbers and text from multiple tabs into one tab with the same headings?
Copy and paste or is there an option to have this automated so summary tab would reflect changes in original tabs?
Thanks
It depends on how you organise the workbook and what changes/volume of data to expect
You can have a master sheet with absolute references to the feeder sheets, in blocks of say, 100 rows, if you expect your data to never need more than 100 rows per feeder sheet. Then do an autosort to clear out empty data ranges. If some sheets have 3 rows of data and others a thousand then that's not going to work. Or use INDIRECT to make an all purpose master sheet, but again it has to be manually controlled for varying range sizes coming from the feeder sheets.
There may be a method of using dynamic named ranges to help define the data you are collecting.
If you are in Excel, probably the best method is to record a macro and then edit the VBA, paying particular notice to how to move through worksheets sequentially, select ranges that have data in them and then to locate bottom of the last data set in your master list before pasting the copied data.
Or copy & paste manually...
A macro that copies first sheet and pastes into new sheet.
Then copies the next sheet and INSERTS that into to the row under your headings.
Removes the need to allow buffer spaces etc
You can do something using PowerQuery in Excel - load the data from each tab as a table, then append the tables in the Query editor to create a new master table then load that to a new worksheet. That'll work asusming all the tabs have the same column headings, but you can always do some wrangling in PowerQuery to sort it out.
(Data tab on the ribbon, 'Get & Transform' group, 'From Table' to get started pulling the data from each tab)
Would simply linking a range of cells in the summary sheet to the cell in the source sheets work? Just type = in the top left cell where you want the data on the summary sheet, then click in the top left of the source and hit Enter, so getting something like =Sheet2!A1. Then drag that cell across as many columns as needed, then drag down as many rows as needed. Then do the same with the next sheet.
Thanks all. I'll bookmark this for later use on other work.
I ended up using the linking sheets similar to the last post. The bloke who asked for it seemed delighted so good job.