Copy Data from Same Cell in Multiple Sheets to One Sheet

[awashington]

Hey y'all!

I have a software that generates excel spreadsheets using a pre-determined template. I merge them into one workbook using a macro that then renames the sheet based on on the sheet's B2 cell value. I currently have indirect formulas on a summary sheet that can pull data from each of the added sheets, but its dependent on a manual entry of the B2 cell value so it can connect to the appropriate sheet name.

I'm trying to get the macro to loop through the worksheets and copy the B2 value into a column (range?) on the summary sheet, until there are no more sheets to copy from.

Basically, I need Sheet#!B2 to be copied to Sheet1!B2:B, where the active cell to enter the data on moves down a row on the destination sheet each loop. So Loop One would pull Sheet2!B2 to Sheet1!B2, and then Loop Two would pull Sheet3!B2 to Sheet1!B3 and so on and so forth. I also need the sheets to be defined by index number, not name.

Sub MatterRename()

    Dim SummarySheet As Worksheet
    Dim ws As Worksheet
    Dim i As Integer

    Set ws = ThisWorkbook.Worksheets(1)
    Set rs = ThisWorkbook.Worksheets.Count
    
    For i = 2 To SummarySheet.Range("B2:B" & SummarySheet.Range("B2").End(xlDown).Row).Cells.Count + 2
    
        ws.Range("B2").Copy Destination:=SummarySheet.Cells(B2)
        
    Next
    
End Sub

where SummarySheet is the sheet I need the data copied to (which would be the 1st in the index), ws is the worksheet the data is being pulled from, and B2 is the location of the data (text) I need to copy across all of the worksheets (except for the SummarySheet)

I get a Run Time Error 13, Type: Mismatch, with Set rs = ThisWorkbook.Worksheets.Count highlighted.

 

[jdellasala]

You may want to watch this video: Excel 3D Formulas Explained (Includes a Bonus Excel Hack!)

 

[awashington]

You may want to watch this video: Excel 3D Formulas Explained (Includes a Bonus Excel Hack!)

I thought about that, but I don't need to sum the data, I just need the data to be copy & pasted. I also couldn't have the formula in the summary sheet to automatically update when I merge in the workbook, since I don't have sheet names to reference. If I'm doing it after, it doesn't save me any time from just copy & pasting the data manually.

 

[jdellasala]

I thought about that, but I don't need to sum the data, I just need the data to be copy & pasted. I also couldn't have the formula in the summary sheet to automatically update when I merge in the workbook, since I don't have sheet names to reference. If I'm doing it after, it doesn't save me any time from just copy & pasting the data manually.

The example is SUM, but you could use formula including a plain =Sheet1!A1 to do the copy. Note that the sheet name needs single quotes if there's a space in the sheet name as in ='Sheet 1'!A1.