Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default HELP CONSOLIDATING SAME RANGE EACH SHEET TO SEPARATE SHEET AND RANGE

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



  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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

wrote in message
news:4ecbb$42bc3467$18311ee2$659@allthenewsgroups. com...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
Find a non-blank cell and bring back text a in same row Rod Excel Worksheet Functions 12 March 24th 05 09:43 PM
Dynamic Print Range Help waxwing Excel Worksheet Functions 2 February 21st 05 03:47 PM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 12:51 AM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 05:57 PM


All times are GMT +1. The time now is 10:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"