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!!
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!!
