Capture sheet names and a particular cell value within a workbook

[amaresh achar]

Hi, I need a VBA / formula solution for one of my tasks:

I have multiple (Identical) worksheets in a workbook, and I need to plot a table with each sheet names and value of cell "B2" against each sheet name.

The table need to be created by adding new worksheet at the end.

Example:

Sheet1name Sheet2name Sheet3name Sheet4name Sheet5name Sheet6name Sheet7name SheetNname
"B2" of sheet1 "B2" of sheet2 "B2" of sheet3 "B2" of sheet4 "B2" of sheet5 "B2" of sheet6 "B2" of sheet7 "B2" of sheetN

Thanks in advance,
Amaresh

 

[Peter_SSs]

Give this a try

Sub SheetSummary()
  Dim i As Long
  
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Values"
  For i = 1 To Sheets.Count - 1
    With Sheets(i)
      Sheets("Values").Cells(4, 2 + i).Resize(2).Value = Application.Transpose(Array(.Name, .Cells(2, 2).Value))
    End With
  Next i
  Sheets("Values").Range("B4:B5").Value = Application.Transpose(Array("Sheet Names", "Values in B2"))
End Sub

 

[amaresh achar]

Yeah.. Works Great.. Thanks Peter_SSs

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[amaresh achar]

You're welcome. Thanks for the follow-up.

BTW, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Yeah.. ok.. got it.. I have update my Office version and Platform details... Thanks for your suggestions..

 

[Peter_SSs]

I have update my Office version and Platform details.

Great, thanks for doing that.

 

[amaresh achar]

Hi, Peter_SSs,

Can you please modify the code to extract the values from G19 and I19 into one sheet.. (But this time i have the data in different xlsx files instead of sheets)

Thank you in advance,
Amaresh.A.R

 

[Peter_SSs]

But this time i have the data in different xlsx files instead of sheets

How would we know which files to look in?

How would we know which sheet(s) in those files to look in G19 and I19?

What cells in what sheet in what file would the results go?

 

[amaresh achar]

Oh.. ? sorry.. I forgot to give those details..

Extractor.xlsm

Sheet1 SN,Workbook Name,Cell G19,Cell I19

docs.google.com

1. I have attached an excel file "Extractor"..
2. when I run the macro from "value extractor", it should pop up file browser window to select the folder path of target Excel files..
3. and it should extract all the values from cell g19 and i19 from sheet 2 of every workbook in the folder and dump it in columnC & columnD respectively
4. and in columnB it should copy the workbook from which g19 & i19 cell values are copied.

Thanks in Advance,
Amaresh

 

[Peter_SSs]

From what I can see this is not a simple adaptation of the earlier code but a separate question. Therefore I suggest that you start a new thread for it.