Copy Last Row & sheet name

stol2J

New Member
Joined
Jul 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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!)

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

stol2J

New Member
Joined
Jul 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
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
 

Forum statistics

Threads
1,141,777
Messages
5,708,465
Members
421,571
Latest member
ChaosPup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top