Getting a value from every sheet in a workbook

[tontondavid10]

I have a workbook with over 100 worksheets. In each sheet, Cell A1 is the only thing i'm really interested in, but I need each of those values in one worksheet so I can plot them. I'm really struggling with this as Excel seems to refer to the worksheets by name rather than index so you can't just drag the table down or across to get this.

 

[Joe4]

Try this. This will add a new sheet at the end, and populate column A with the values from A1 of all the other sheets.

Sub GetValues()

    Dim ws As Long
    Dim totShCt As Long
    
    Application.ScreenUpdating = False

'   Get total sheet count
    totShCt = Sheets.Count
    
'   Add new worksheet and move to end
    Worksheets.Add
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    
'   Populate column A of final sheets with values from cell A1 on all other sheets
    For ws = 1 To totShCt
        Sheets(totShCt + 1).Cells(ws, "A") = Sheets(ws).Range("A1")
    Next ws
    
    Application.ScreenUpdating = True
        
End Sub

 

[rpaulson]

Looks like Joe, beat me to the punch,

this quick macro should work, try it on a copy of your file.

Sub Do_it()
Dim ws As Worksheet
r = 2
For Each ws In ActiveWorkbook.Worksheets
    Cells(r, "A") = ws.[A1]
    r = r + 1
Next ws
End Sub

hth,
Ross

 

[Joe4]

this quick macro should work, try it on a copy of your file.

I think you may want to include your assumptions and caveats.
In order for that to work properly, it assumes that:
- you already have a sheet that you want to write these values to
- the sheet you want to write these values to is the first sheet in your workbook
- you are on the first sheet when you run the code

If going this route, I would recommend a few things:
- a confirmation pop-up asking if they have already created a sheet for logging, and it is the first sheet
- physically activating the first sheet at the top of the code to avoid it writing to the wrong place (and overwriting data)

 

[rpaulson]

Valid Points Joe

 

[mjmcder]

Try this. This will add a new sheet at the end, and populate column A with the values from A1 of all the other sheets.

Sub GetValues()

    Dim ws As Long
    Dim totShCt As Long
    
    Application.ScreenUpdating = False

'   Get total sheet count
    totShCt = Sheets.Count
    
'   Add new worksheet and move to end
    Worksheets.Add
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    
'   Populate column A of final sheets with values from cell A1 on all other sheets
    For ws = 1 To totShCt
        Sheets(totShCt + 1).Cells(ws, "A") = Sheets(ws).Range("A1")
    Next ws
    
    Application.ScreenUpdating = True
        
End Sub

I'm amazed at how little code it takes to perform this. I am a newbie and have the need for a similar task, and this is so efficient compared to the way I was attempting to navigate the situation. One question Joe, this would work with changing the names of the tabs, etc correct?

 

[Joe4]

One question Joe, this would work with changing the names of the tabs, etc correct?

Yes, it is totally independent of tab names. It is working off their indexes (locations within the workbook).
Essentially, it will always add a new sheet to the end of the workbook, and do the summary there.

 

[tontondavid10]

In the end I used a really sloppy but efficient method that combines both of my problems (see my other thread concerning getting the average value from each sheet https://www.mrexcel.com/forum/excel-questions/1026756-average-tables-varying-sizes.htmlml). The original problem was that I have several spreadsheet in a single workbook. I wanted to get the average of the values from each worksheet into a single table so I could plot trendlines, and here is the complete sub:

Sub AveragePerSheet()




Dim i As Integer, nSheets As Integer
nSheets = Sheets.Count


For i = 1 To nSheets - 1
    Sheets(i + 1).Cells(1, 1).FormulaR1C1 = "=average(R[1]C[1]:R[200]C[200])"
    Sheets(1).Cells(i, 1) = Sheets(i + 1).Cells(1, 1)
Next i


End Sub

As I sais, quick and dirty, but in essentially 4 lines of code I could get the average for each sheet and copy the value over to the collated table. There was a lot of frustration getting this done because Excel seems to prefer working with sheet and cell names rather than their indices, which are much easier to handle in a macro.