How can i get all my data in workbook into one sheet

[dougers1]

Hi

i have a workbook at work that has Tabs along bottom for each month - Each month has around 50,000 rows of data. i want to combine all the data into one sheet but if i try copy n paste i get this MSG

we cant copy and paste because the area are'nt the same size

each month has the same headings so im confused why it wont paste


how can i get all data in workbook into one sheet - i use excel 2013

[alansidman]

1. Please update your profile to reflect the version you are using.
2. Using Power Query you can append all of the tabs to one sheet. Here is a video showing how.

https://www.youtube.com/watch?v=HcfKOusOJhA

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

[Logit]

Here is one of any number of alternative methods ...

The following assumes you have a sheet named SHEET1 and this sheet is completely blank. All other sheets (data) will be copied to this sheet :

Please Login or Register  to view this content.

The current code is set to copy each sheet, range includes cell A2 to column F and down to the last used row of data.
You will need to adjust the range to match your sheets.