Reference range of cells From Multiple Worksheets Into One Master Sheet With VBA Code

[cpmurray1985]

Hello!

I would like to copy a range of cells from all worksheets in a notebook into one sheet. I found this code on ExtendOffice, and it almost works as I want it, but it will only copy the active cell, or the cell clicked on from all sheets. I would like to be able to copy the contents from a range of cells, for example, D2:T4, and have it paste in a new worksheet exactly as it was copied in the same spots, even if there are missing values as the value of the range of cells differ from each workbook, some have data and some do not.

Thank you!

Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub