Macro for copying and inserting copied cells on multiple spreadsheets

[EdieS]

I have multiple spreadsheets that have cells linked between the spreadsheets. I would like with a macro to select the last row with data in in one spreadsheet and the 6 rows above that row and Copy and Insert copied cells below the last row with data and then loop through the other spreadsheets doing the same copy/insert so that the linked cells stay in sync.

Thanks for any help.

Edie

 

[AlphaFrog]

Sub Copy_Last6()

    Dim ws As Worksheet
    Dim Lastrow As Long
    
    For Each ws In Worksheets
    
        Lastrow = 0
        On Error Resume Next
        Lastrow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        On Error GoTo 0
        
        If Lastrow >= 6 Then
            ws.Rows(Lastrow - 5).Resize(6).Copy Destination:=ws.Rows(Lastrow + 1).Resize(6)
        End If
    
    Next ws

End Sub

 

[EdieS]

This works perfectly thank you very much.