Extract data in specific cells in multiple worksheets using VBA then copy to new sheet report

[Eddie Vidaurre]

Hi there,

Can someone please help me ? I have a workbook with 45 sheets. Each sheet corresponds to each week of the year. In other words I will end up with 52 once the year is finished. Now within each sheet there is a dashboard (see Capture 2) which contains a specific value in the same cells (M14 - M16) throughout all 45 sheets. I need to plot these values in a new sheet in three different rows each in a new cell by each corresponding week (see Capture 1) S1 = Week 1, S2 = Week 2 and so on until Week 45.  Thank you. 

Regards,

Eddie

Capture 2Capture 1

[#2]

have you tried using "Consolidate" command in data-->data tools? it helps you create a single sheet consolidating all the data in seperate sheets. You need to create names of these ranges on seperate sheets first than consolidate all these names in to one page.

Alternatively you can use indirect to get the data you need. Create a table with sheet names and cell addresses like sheet names in A, and cell name in B column, and use the cell and use the information in a indirect formula like:
=INDIRECT("'"&1A1&"'!" &B1)

power pivot can also help but I do not have experience in it.

edit: typo

[#3]

Thank you for your response. I actually got it to work with the following formula =INDIRECT("'ReuniónS("&ROWS(D$3:D3)&")'!M15") but the only thing is it works listing the values in a column. I really need it to list them in a row but it repeats the same value across the row. See attached. Capture 3Capture 4

[#4 · Accepted solution]

The ROWS(D$3:D3) will not produce any different result in seperate columns if you copy the formula in the same row. You can try using COLUMNS instead if you are spreading this formula in columns.

[#5]

That's what I thought as well so I did =INDIRECT("'ReuniónS("&COLUMNS(F$13:F$13)&")'!M15") but I still get the same result "4". 

[#6]

change it to: COLUMNS($F$13:F$13)

[#7]

Yes that did it !!! Thanks alot for your help !!!