Combining Multiple Worksheets with Same Table Headers

[Bouchard17]

Hi all,

im new the land of VBA coding and only really learnt the absolute basics so far.

every month i have to analyse data in a worksheet that contains 13 tabs (One for each branch/Office). Each tab has the data laid out in the same way (Same headers) with some cells locked to avoid users overwriting the formula's.

Im looking for a macro to combine all the worksheets into one new worksheet which will make my analysis easier. rather than creating multiple pivots for each branch.

Any Helps is appreciated.

Amy

[p45cal]

What version of Excel are you using?
Any version from 2013 should support what's now built-in to Excel: a flavour of Power Query/Get & Transform Data, which for repetitive tasks would be an ideal solution, especially if you do further analysis of the resultant sheet and even more especially if you make a pivot table from that data - to the point that you may need do little more than refresh a pivot table.

Perhaps an attached workbook 2 or 3 dummy sheets with handful of rows of dummy data would spur us on to give you a solution.

[Bouchard17]

im using Excel 2016 but i cant see the power query when searching, i will need to google later this evening. i will attach a sample later this evening for you

thanks for replying

[p45cal]

In Excel 2016 it's on the Data tab of the ribbon, in the Get & Transform section, Show Queries, New Query, From Table etc.
Look forward to your file.