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]

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

Thanks for checking it on your side and for sharing the solution in the first place. I will trouble-shoot mine to get it working.

 

[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

I found the issue, small edit to your original code adding ' into the dictionary item for the worksheet name. Without the ' , my version of excel O365 was creating external links (even thought same workbook) and these links were being asked to be updated each time the CollateData macro ran. Everything working now! Thanks NoSparks.