HELP Multiple worksheets need data from Cell T4 on each one

[mstovall21]

I'm trying to get a non-numerical data from Cell location T4 from multiple worksheets into a new one, where it lists it down 1 column What's the best way to do this? Essentially I'm trying not to manually cut and paste cell T4 from each into a new worksheet. Also, I'm not all that familiar with Macros, so any help or tips would be much appreciated! Thanks!

 

[jbeaucaire]

Something like this will "Create" a new sheet, then put a list in columns A:B showing the source sheetname and T4 value from each. This should get you started.

Option Explicit

Sub CreateList()
Dim SummaryStr As String, NR As Long
Dim ws As Worksheet, wsSum As Worksheet

SummaryStr = "Summary"      'this is the name of the sheet to collect into

If Not Evaluate("ISREF('" & SummaryStr & "'!A1)") Then
    Worksheets.Add After:=Sheets(Sheets.Count).Name = SummaryStr
Else
    Sheets(SummaryStr).ClearContents
End If

Set wsSum = Sheets(SummaryStr)
NR = 1

For Each ws In Worksheets
    If ws.Name <> wsSum.Name Then
        wsSum.Range("A" & NR).Value = ws.Name
        wsSum.Range("B" & NR).Value = ws.[T4].Value
        NR = NR + 1
    End If
Next ws

End Sub

You should only have to edit the part in red to the sheet name you want to create, or leave as is to try the first time.

 

[mstovall21]

Hi! Thanks for your help. Got a question though. How/where do I define what location/files to pull the data from the T4 cells. Basically how do I define the directory path and file names? Like, I said, I'm not all that familiar with macros. lol