Option Explicit
Sub Button4_Click() Const RangeAddress As String = "H4:AD600" ' Source Range Address Dim SourceRange As Range ' Source Range Dim i As Long ' Worksheets Counter With ThisWorkbook ' Define and copy Source Range in First Worksheet to clipboard. Set SourceRange = .Worksheets(1).Range(RangeAddress) SourceRange.Copy ' Paste Source Range into the remaining worksheets. For i = 2 To .Worksheets.Count .Worksheets(i).Range(RangeAddress).PasteSpecial xlPasteFormulas Next i ' Select range 'A1' in all worksheets and activate first worksheet. For i = .Worksheets.Count To 1 Step -1 .Worksheets(i).Activate .Worksheets(i).Range("A1").Select Next i End With ' Remove Source range from clipboard. Application.CutCopyMode = False ' Inform user that the operation has finished. MsgBox "Copied Range(" & RangeAddress & ") from the first to " _ & "the remaining worksheets.", vbInformation, "Copy Range"
End Sub