Copy multiple cell data from multiple sheets in a workbook into one

MIAKA

New Member
Joined
Nov 23, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that contains 150 sheets with required data sitting in cells A13:AD116 in each of these sheets. The sheets have no identifier to differentiate the data except the sheet name which are also sporadic eg 0101Food; 02050102Merchandise.
Summary sheet I have created headers for each column.
Column A = Sheetname
Column B:AE will be the same as in the 150sheets.


I have been doing what I can by a simple cell reference [ B2= 'Sheetname'!A13 ]and copying it across to column AE and then dragging down 104 rows. Then I copy the block for the next sheet, then the top row find and replace row numbers; find and replace Sheet names. I was hoping someone might have a suggestion on what could be done. I tried to look at using the Get Data but the workbook has no defined tables etc and wouldn't pick up all the sheets I needed.
EG Summary Sheet
Cell A2 = Sheet Name
Cell B2 = A13
Cell C2 = B13
.
.
.
Cell AE2 = AD116

Thanks in advance!!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top