Return cell value from multiple worksheets

[jademarie]

So I've done some searching and it looks like I need a VBA, but being a total rookie when it comes to codes I couldn't find anything that I could 100% work with without messing it up.

I have up to 50 worksheets in my workbook all named individually such as GD01, EX02, LD01 etc
I would like to add a summary page which includes the value located in cell K13 of each sheet. I would also like to include the sheet name next to the cell value so I know which piece of equipment the value applies to. It would be best to start from cell A2 so there is room for headers/filters

The only problem is that there is one sheet I wish to be excluded, its called 'Equipment Inventory List'

Is there any way I am able to do this?

Thanks in advance!

 

[Kenneth Hobson]

Welcome to the forum!

Add a comma and the sheet name to exclude other sheets in the first Case.

Put code into a Module. Run with sheet to add data as active sheet.

Sub Main()
  Dim i As Integer, n$, calc As Integer, r As Range
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  calc = Application.Calculation
  Application.Calculation = xlCalculationManual
  
  Set r = Cells(Rows.Count, "A").End(xlUp).Offset(1)
  For i = 1 To Worksheets.Count
    n = Worksheets(i).Name
    Select Case n
      Case "Equipment Inventory List"
      Case Else
        r.Value = Worksheets(i).Range("K13").Value
        r.Offset(, 1).Value = Worksheets(i).Name
        Set r = r.Offset(1)
    End Select
  Next i
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  calc = Application.Calculation
  Application.Calculation = xlCalculationManual
End Sub

 

[NiMip]

What you're asking for is similar to something I've come across before, except for the part of excluding a specific sheet. I can get you started but I might need to think about a good way to exclude a sheet name without it just leaving a blank in its place.

Firstly, credit where credit is due: 99% of this solution comes from the following link. Heads up; this just links to a webpage, but his "formula based solution" downloads a spreadsheet, in case you're worried about security: http://www.ashishmathur.com/generate-a-list-of-all-tabs-names-without-using-vba/

The solution for your first part about a list of sheet names and the value from K13 of each one should be as follows:

1. Go to Formulas > Define Name, give it a name (e.g. "Sheets") and where it asks "Refers to:" enter the following formula =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
2. In cell A2, enter the formula =IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"") and fill down as many rows as you have sheets
3. In cell B2, enter =INDIRECT("'"&A2&"'!K13") and fill down the rows

The part about excluding a sheet name... Included in the spreadsheet I talked about above is also the solution for excluding the page that the formula is written on, which will likely be a good place to start for removing the Equipment Inventory List sheet. I've only had a quick glance at the formula, but it's going to take more than that for me to figure out how it works. Will have to give me a day or so to find the time, or maybe someone else will figure it out haha

 

[NiMip]

Actually, that wasn't so bad. In step 2, instead of the formula I provided, enter this one instead, and press CONTROL+SHIFT+ENTER instead of just enter, as it's an array formula:
=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),SMALL(IF(MID(Sheets,FIND("]",Sheets)+1,255)<>"Equipment Inventory List",ROW(INDIRECT("$A$1:$A$"&COUNTA(MID(Sheets,FIND("]",Sheets)+1,255))))),ROW(1:1)),1),"")

I've coloured the name of the sheet you don't want in the formula so you can see where to adjust things if the name changes. You could also make this a reference to another cell (say $D$2) and just type the name into that cell instead, so you don't have to edit the formula and fill down the rows if you want to make a change to the sheet name.

 

[jademarie]

Thank you!
I wont be able to try it out for a few days now but I will respond back with how it all goes. Thanks again