SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-287030%22%20slang%3D%22en-US%22%3EExtract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287030%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20me%20%3F%20I%20have%20a%20workbook%20with%2045%20sheets.%20Each%20sheet%20corresponds%20to%20each%20week%20of%20the%20year.%20In%20other%20words%20I%20will%20end%20up%20with%2052%20once%20the%20year%20is%20finished.%20Now%20within%20each%20sheet%20there%20is%20a%20dashboard%20(see%20Capture%202)%20which%20contains%20a%20specific%20value%20in%20the%20same%20cells%20(M14%20-%20M16)%20throughout%20all%2045%20sheets.%20I%20need%20to%20plot%20these%20values%20in%20a%20new%20sheet%20in%26nbsp%3Bthree%20different%20rows%20each%20in%20a%20new%20cell%20by%20each%20corresponding%20week%20(see%20Capture%201)%20S1%20%3D%20Week%201%2C%20S2%20%3D%20Week%202%20and%20so%20on%20until%20Week%2045.%26nbsp%3B%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEddie%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20757px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59837i158C649B3F6D4DEF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture%202.PNG%22%20title%3D%22Capture%202.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECapture%202%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59838i79CFAF8C45B9F9D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECapture%201%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-287030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287148%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287148%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20that%20did%20it%20!!!%20Thanks%20alot%20for%20your%20help%20!!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287142%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287142%22%20slang%3D%22en-US%22%3E%3CP%3Echange%20it%20to%3A%20COLUMNS(%24F%2413%3AF%2413)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287141%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287141%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20what%20I%20thought%20as%20well%20so%20I%20did%20%3DINDIRECT(%22'Reuni%C3%B3nS(%22%26amp%3BCOLUMNS(F%2413%3AF%2413)%26amp%3B%22)'!M15%22)%20but%26nbsp%3BI%20still%20get%20the%20same%20result%20%224%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287132%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287132%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20ROWS(D%243%3AD3)%20will%20not%20produce%20any%20different%20result%20in%20seperate%20columns%20if%20you%20copy%20the%20formula%20in%20the%20same%20row.%20You%20can%20try%20using%20COLUMNS%20instead%20if%20you%20are%20spreading%20this%20formula%20in%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287123%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287123%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response.%20I%20actually%20got%20it%20to%20work%20with%20the%20following%20formula%20%3DINDIRECT(%22'Reuni%C3%B3nS(%22%26amp%3BROWS(D%243%3AD3)%26amp%3B%22)'!M15%22)%20but%20the%20only%20thing%20is%20it%20works%20listing%20the%20values%20in%20a%20column.%20I%20really%20need%20it%20to%20list%20them%20in%20a%20row%20but%20it%20repeats%20the%20same%20value%20across%20the%20row.%20See%20attached.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20341px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59851iE1BB5F3FD08D9F0E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture%203.PNG%22%20title%3D%22Capture%203.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECapture%203%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20304px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59852i9545B6F0A703249D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture%204.PNG%22%20title%3D%22Capture%204.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECapture%204%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287114%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20data%20in%20specific%20cells%20in%20multiple%20worksheets%20using%20VBA%20then%20copy%20to%20new%20sheet%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287114%22%20slang%3D%22en-US%22%3E%3CP%3Ehave%20you%20tried%20using%20%22Consolidate%22%20command%20in%20data--%26gt%3Bdata%20tools%3F%20it%20helps%20you%20create%20a%20single%20sheet%20consolidating%20all%20the%20data%20in%20seperate%20sheets.%20You%20need%20to%20create%20names%20of%20these%20ranges%20on%20seperate%20sheets%20first%20than%20consolidate%20all%20these%20names%20in%20to%20one%20page.%3CBR%20%2F%3E%3CBR%20%2F%3EAlternatively%20you%20can%20use%20indirect%20to%20get%20the%20data%20you%20need.%20Create%20a%20table%20with%20sheet%20names%20and%20cell%20addresses%20like%20sheet%20names%20in%20A%2C%20and%20cell%20name%20in%20B%20column%2C%20and%20use%20the%20cell%20and%20use%20the%20information%20in%20a%20indirect%20formula%20like%3A%3CBR%20%2F%3E%3DINDIRECT(%22'%22%26amp%3B1A1%26amp%3B%22'!%22%20%26amp%3BB1)%3CBR%20%2F%3E%3CBR%20%2F%3Epower%20pivot%20can%20also%20help%20but%20I%20do%20not%20have%20experience%20in%20it.%3C%2FP%3E%3CP%3Eedit%3A%20typo%3C%2FP%3E%3C%2FLINGO-BODY%3E
Eddie Vidaurre
New Contributor

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 2.PNGCapture 2Capture.PNGCapture 1

6 Replies

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

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 3.PNGCapture 3Capture 4.PNGCapture 4

 

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.

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". 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies