Loop through Sheets to get single value

[GeeWhiz7]

Hi guys,
I have been browsing through the threads in Mr. Excel and can't quite find what I am looking for.
I have a workbook with >200 sheets that I would like to loop through each one and grab a single value to put into a summary sheet at the front of it all.

Something like below,

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Wksheet	Value			Section 1A	Section 2A	Section 3A	Section 4A	Section 5A	Section 6A	Section 7A
2	Section 1A	-0.75		Value in $B$4=	-0.75	-0.71	0.52	-0.22	0.83	0.01	0.04
3	Section 2A	-0.71
4	Section 3A	0.52
5	Section 4A	-0.22
6	Section 5A	0.83
7	Section 6A	0.01
8	Section 7A	0.04
9	Summary Worksheet				Individual Worksheets
Sheet1

I have tried a few things, but can't quite get the result I am looking for.
Thinking something like this might work at a simple level, but with so many sheets, its hard to know if all the names/values correctly match up.
Any ideas are greatly appreciated! Thanks

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer

i = 1
    For Each ws In Worksheets
        If ws.Name <> "Summary Sheet" Then
           ws.range("$B$4").Copy Destination:=Sheets("Summary Sheet").Cells(B,i)
        End If
i = i +1
    Next

End Sub

 

[GeeWhiz7]

Hi guys,
I have been browsing through the threads in Mr. Excel and can't quite find what I am looking for.
I have a workbook with >200 sheets that I would like to loop through each one and grab a single value to put into a summary sheet at the front of it all.

Something like below,

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Wksheet	Value			Section 1A	Section 2A	Section 3A	Section 4A	Section 5A	Section 6A	Section 7A
2	Section 1A	-0.75		Value in $B$4=	-0.75	-0.71	0.52	-0.22	0.83	0.01	0.04
3	Section 2A	-0.71
4	Section 3A	0.52
5	Section 4A	-0.22
6	Section 5A	0.83
7	Section 6A	0.01
8	Section 7A	0.04
9	Summary Worksheet				Individual Worksheets
Sheet1

I have tried a few things, but can't quite get the result I am looking for.
Thinking something like this might work at a simple level, but with so many sheets, its hard to know if all the names/values correctly match up.
Any ideas are greatly appreciated! Thanks

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer

i = 1
    For Each ws In Worksheets
        If ws.Name <> "Summary Sheet" Then
           ws.range("$B$4").Copy Destination:=Sheets("Summary Sheet").Cells(B,i)
        End If
i = i +1
    Next

End Sub

Was thinking that maybe instead of copy and pasting the value, it would be better to copy and paste the reference to the formula, then when values in each sheet change the summary table is up to date without a macro running.

 

[NoSparks]

give this a try

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer
    Dim dict As Object
   
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    Set ws = Sheets(i)
    If ws.Name <> "Summary Sheet" Then
        dict.Add ws.Name, ws.Range("B4").Value
    End If
Next i

Sheets("Summary Sheet").Range("B2").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))

End Sub

 

[JEC]

@NoSparks

Sheets("Summary Sheet").Range("B2").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))

 

[GeeWhiz7]

give this a try

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer
    Dim dict As Object
  
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    Set ws = Sheets(i)
    If ws.Name <> "Summary Sheet" Then
        dict.Add ws.Name, ws.Range("B4").Value
    End If
Next i

Sheets("Summary Sheet").Range("B2").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))

End Sub

Thank you NoSparks, for getting the value this works perfectly.

One question, is there a way to use the same thing to copy the formula from the worksheets into the summary sheet so they would stay live/dynamic when the value in B4 changed?

 

[GeeWhiz7]

Thank you NoSparks, for getting the value this works perfectly.

One question, is there a way to use the same thing to copy the formula from the worksheets into the summary sheet so they would stay live/dynamic when the value in B4 changed

sorry, a little slow on the update. Changing the .value to .formula does that. Thank you NoSparks!

 

[GeeWhiz7]

sorry, a little slow on the update. Changing the .value to .formula does that. Thank you NoSparks!

Except it doesn't bring the sheet name with it so the formula doesn't work. Can i CONCAT with dict.items the ws.Name somehow to get the summary sheet to carry the full formula?

 

[NoSparks]

try like this

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer
    Dim dict As Object
 
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    Set ws = Sheets(i)
    If ws.Name <> "Summary Sheet" Then
        dict.Add ws.Name, "=" & ws.Name & "!B4"
    End If
Next i

'Sheets("Summary Sheet").Range("B2").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))

' same thing as 2 separate instructions
With Sheets("Summary Sheet")
    .Range("B2").Resize(dict.Count) = Application.Transpose(dict.keys)
    .Range("C2").Resize(dict.Count) = Application.Transpose(dict.items)
End With

End Sub

 

[GeeWhiz7]

try like this

Sub CollateData()

    Dim ws As Worksheet
    Dim i As Integer
    Dim dict As Object
 
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To Worksheets.Count
    Set ws = Sheets(i)
    If ws.Name <> "Summary Sheet" Then
        dict.Add ws.Name, "=" & ws.Name & "!B4"
    End If
Next i

'Sheets("Summary Sheet").Range("B2").Resize(dict.Count, 2) = Application.Transpose(Array(dict.keys, dict.items))

' same thing as 2 separate instructions
With Sheets("Summary Sheet")
    .Range("B2").Resize(dict.Count) = Application.Transpose(dict.keys)
    .Range("C2").Resize(dict.Count) = Application.Transpose(dict.items)
End With

End Sub

This does copy the formula so it works more or less getting the formula and sheet reference into the summary cell, but it has a strange thing it does when I run it. It opens a window to "Update Values:" and is looking for a filename.

 

[NoSparks]

Works fine for me and I can't re-create what you're describing so I have no idea, sorry