Assume the consolidation sheet is name Summary and it exits
Assume only summary and the sheets to be copied are in the workbook
Assume the sheet level name on each sheet is ABCD

Sub CopyData()
Dim sh as Worksheet
Dim rng as Range
dim rng1 as Range
activeworkbook.Worksheets("Summary").Cells.Clear
for each sh in ActiveWorkbook.Worksheets
if lcase(sh.name) < "summary" then
set rng = sh.Names("ABCD").RefersToRange
set rng1 = worksheets("Summary").Cells(rows.count,1).End(xlup ) _
.Offset(1,0)
rng1.Value = sh.Name
rng.copy
rng1.Offset(1,0).PasteSpecial xlValues
rng1.Offset(1,0).Pastespecial xlFormats
end if
next

--
Regards,
Tom Ogilvy


wrote in message
om...
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