Macro to search and record multiple worksheets

[Lockheed]

Hi all,

I would really appreciate some help with developing, what I believe should be, a relatively simple solution:

Currently, I'm exporting data from my company dashboard for around 50 projects. The data for each project is populated in individual sheets, all of which have the same structure, just different numbers populating them. What I would like to have is a summary page and a macro that:
1. Writes the name of the worksheet
2. Then searches through the worksheet and records the array A14:A16
3. Then moves onto the next sheet and repeats.

Any tips/hints/solutions will be much appreciated!

Thanks,
Chris

 

[Leith Ross]

Hello Chris,

This macro will check all the worksheets in the workbook. The macro is setup to use "Summary" as the worksheet name where all the data will be collected. You can change this name to match the worksheet name you are using for this.

The worksheet name and the data in range "A14:A16" are copied into column "A" starting at row 2 on the "Summary" worksheet. The next worksheet will be entered below it at A6. You can also change the starting cell to where you want the data to be.

Sub SummarizeSheets()

    Dim row     As Long
    Dim SumRng  As Range
    Dim SumWks  As Worksheet
    Dim Wks     As Worksheet
    
      ' Name of the summary worksheet. Change this name if you want.
        Set SumWks = Worksheets("Summary")
        
      ' Sarting cell on the summary worksheet. Headers are assumed to be in row 1.
        Set SumRng = SumWks.Range("A2")
        
            For Each Wks In Worksheets
                If Wks.Name <> SumWks.Name Then
                    SumRng.Value = Wks.Name
                    SumRng.Offset(row + 1, 0).Resize(2, 1).Value = Wks.Range("A14:A16").Value
                    row = row + 4
                End If
            Next Wks
        
End Sub

l

 

[Lockheed]

Thanks Leith, works a charm!