Copy Last Row & sheet name

[stol2J]

Hi,

I'm new to VBA and would really appreciate your help here.
I need to copy the last row of each sheet in a workbook to a "Summary Sheet" along with the sheet name as column A. I am pasting the last row from column A to column Y. It is an average of the columns above it, which is why I am using a paste special function.

I'm going off this code below, but it is lacking a few key elements
1) I don't know how to set Column A so it is the name of the sheet that I pasted from (ex "Sheet A')
2) I don't know how to apply this to the entire workbook so I don't have to manually rewrite the macro from "Sheet A" to "Sheet B" etc and the next sheet gets pasted to the next empty row. I am working with over 1000 sheets so changing manually is not possible (if this is possible!)

Option Explicit
Sub CopyRow()
Dim lastrowSrc As Long
Dim lastrowDest As Long
 'Get last row of data
 lastrowSrc = Sheets("Sheet A").Range("A" & Rows.Count).End(xlUp).Row
 
 'Get first blank row (last row of data +1)
 lastrowDest = Sheets("Summary Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
 
 'Copy row
 Sheets("Sheet A").Range("A" & lastrowSrc).Resize(, 25).Copy
Sheets("Summary Sheet").Range("A" & lastrowDest).PasteSpecial xlValues
End Sub

Thanks very much for your help. Since I needed to reference multiple sheets, I didn't upload a mini sheet but I can add a dropbox link or something else if that would be helpful.
Best,
Serena

 

[stol2J]

Okay, so I ended up finding code to create an array (code below). But I am still struggling with inserting the sheet name as column A. If anyone has any suggestions, please let me know

Sub Master2()

    Dim arr()   As Variant
    Dim ws      As Worksheet
    Dim wMast   As Worksheet
    Dim x       As Long
    Dim y       As Long
        
    Set wMast = Sheets("SummarySheet")
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> wMast.Name Then
                x = .Cells(.Rows.Count, 1).End(xlUp).Row
                y = .Cells(x, .Columns.Count).End(xlToLeft).Column
                arr = .Cells(x, 1).Resize(, y).Value
                wMast.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(arr, 2)).Value = arr
                Erase arr
            End If
        End With
    Next ws
    
    Application.ScreenUpdating = True
    
    Set wMast = Nothing
    
End Sub

 

[stol2J]

ended up using this as a solution: How To Generate A List Of Sheet Names From A Workbook Without VBA | How To Excel
I think I should be set!