HELP CONSOLIDATING SAME RANGE EACH SHEET TO SEPARATE SHEET AND RANGE

[Guest]

I need a macrocode to bring a specific data range, the same name
range, from multiple individual worksheets in the same workbook to a
separate consolidating sheet within the workbook. Each sheet will have
it's own unique name and the data will go into it's own individual range
on the consolidating sheet. Just to complicate matters, new sheets will
constantly be added to the workbook.
I would like to have it run when the consolidating sheet is open.
If you can help, I would be most grateful! I am an experienced Excel
user, but only a beginner VBA code writer. I feel it will take me a
while to learn the code development well enough and I am on a time
constraint.
Thanks, Areidski

 

[Bernie Deitrick]

Areidski,

Try the macro below. Change the "NamedRange" string to that of the name of
the actual range that you want to copy.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim mySht As Worksheet
Dim SumSht As Worksheet
Dim strName As String

strName = "NamedRange"

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Application.DisplayAlerts = True

Set SumSht = Worksheets.Add
SumSht.Name = "Summary Sheet"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Summary Sheet" Then
mySht.Range(strName).Copy _
SumSht.Range("A65536").End(xlUp)(2)
End If
Next mySht

End Sub