Compiling Table using multiple sheets that are all the same.

[CaptainCactus]

Hey guys,


I have a template made for a research program that I am doing in agriculture that tracks soil nutrients in plants. The template is the same for every time we collect data, so the boxes are the same. I have compiled all the sheets into one book and i am wondering how I can compile data from those sheets into a single table in which i can then graph the results.


The problem i am trying to figure out: For example, in box A10 for every single sheet, that is the soil nutrient content value for that particular table of plants. How, either using VBA or a macro, take the value in box A10 from 30 sheets and turn that into a table of all of those values? I am not looking to sum those values, but rather take the raw calue and put them into a table that I can graph.

ME 2010 is what i am using.


Thanks,

[xladept]

Hi CC,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[FJT]

I will tell you how I would do this - although not as requested with VBA and macros.
Create a new tab in your document. Rather than type each sheet name in column A, enter this formula in A1:
=IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A1:A$1)),"")
Drag down to row 30 - or however many tabs you have.

In cell B1 enter: =INDIRECT("'"&A1&"'!a10"), drag down to row 30.
You then have your data for graphing.

[bakerman2]

Basic but it gives you an idea how you can solve this. Summary sheet must be first sheet.

Please Login or Register  to view this content.

[CaptainCactus]

Will this sample work?

I'm looking to take 30-40 sheets that all have a different value in a single cell, for this example, i am using b3.

i want to compile them into a table to make a graph with.

Thanks guys!

[xladept]

Here's a quickie:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

[CaptainCactus]

After closing the window with the macro saved as code, how do place that into a sheet i am working on?

[xladept]

It doesn't need to be in the sheet you're working on - but your sheet must be the active one - with your sheet active you can hit ALT+F8 to get a list of macros then just doubleclick on the name(CptCactus) and it will run

[bakerman2]

Another one which improves execution time with 75%.

Please Login or Register  to view this content.

[bakerman2]

Always nice to get positive feedback from a Forum Guru.
Many thanks for the rep.

[xladept]

@bakerman2 - You're more than welcome, I'm already using your coding technique