Excel - Copy/Paste last row to row below it & repeat across multiple sheets.

[EXCELlent_name]

Hello all,

I'm working on automating a report that is simple but time-consuming. Effectively, I am copying a range from the last row of 8 sheets and pasting one row down (essentially, just carrying over formulas prior to hardcoding the historical data). Since I'm new to VBA I decided to go one piece at a time and focus on the copy/paste step first.

I started here:

Sub CopyPaste()


Worksheets("RetailIndustry").Activate
    Range("K50:R50").Select
    Selection.Copy
    Range("K51").Select
    ActiveSheet.Paste
    
    Worksheets("RetailBrand").Activate
    Range("K50:BN50").Select
    Selection.Copy
    Range("K51").Select
    ActiveSheet.Paste
    
'Repeats through remainder of worksheets
End Sub

This works perfectly as a one-off function. But I'd like to add a dynamic element to the range since a new row is added each time this is run. So I started playing with Range("k3").End(xlDown).Offset(1, 0).Select; however, I then ran into problems where I could copy a single cell but had errors when attempting to integrate a range function. I did find a few alternative methods online but these used .EntireRow which caused unwanted cells to be included.

Finally, I stumbled upon a solution and with a little tweaking I have something like this:

Sub CopyLastRow()
    
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("RetailIndustry")
    
    Dim lastRow As Long
    lastRow = sourceSheet.Range("K" & sourceSheet.Rows.Count).End(xlUp).Row
    
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("K" & lastRow & ":R" & lastRow)
    
    sourceRange.Select
Selection.Copy
Range("k3").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

End Sub

This does a beautiful job of capturing the cells that I desire. Unfortunately, I don't understand it very well and I'm having issues extending it to the other sheets. Should I be somehow ending the "Dim" function so that I can reference new sheets? Or is there a better way set this up for multiple sheets? Thanks everybody for the help!

 

[mumps]

Will the source range always start in column K? Will the last column in the source range be different in each sheet? Do you want to do this in all the sheets in your workbook or only in some sheets. If only in some sheets, what are the sheet names?