Extract cell value from multiple worksheets onto a new worksheet.

see here you need to copy all the code into a new module
http://msdn.microsoft.com/en-us/library/cc793964.aspx
read the comments
relevent ones you may like to change are
Specify the range to place the data. Set CopyRng = sh.Range("A1:G1")
so change to your cell here

' Optional: This statement will copy the sheet
' name in the H column.
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

delete this bit if not needed

Not knowing the specifics of how your sheets are named, you might try adapting this bit of code which assumes the very last sheet is the intended target sheet to receive the data. I assumed sheets are named "sheet1, Sheet225, etc.)

Cell B4 in the other sheets with be copied down column-A of the last sheet.

Please Login or Register  to view this content.

If the sheet names are all sequentially numbered (Sheet1, Sheet2, Sheet3, etc....or something as consistent as that), then you can do this with a formula, too. A volatile function called INDIRECT() would work.

On the 301st sheet, you could enter this formula anywhere and copy it down 300 cells:

=INDIRECT("Sheet" & ROWS($1:1) & "!B4")

If your sheetnames ever become NOT that sequential or easy, you can change the indirect() formula to draw the sheet name from an adjacent cell where you've listed off your sheetnames. For instance if your sheet names are in column B starting at B2, you could put this in C2 and copy down:

=INDIRECT("'" & B2 & "'!B4")

Notice this is a *tiny* bit different with red apostrophes added, this is to make sure the references created still work if your sheet names happen to be more than one word.

===============
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

dmkolb welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.