Consolidate by range names which represent worksheet tab names

  • Thread starter Thread starter Wally
  • Start date Start date
W

Wally

I would like consolidate by range names, which most often is the tab
names, with the exception if the tab name starts with a number (ie. -
35Wil would have a range name of _35Wil)

My first range and sheet name is always Total and the last is the
sheet before IncStmt.

There can be anywhere between one and 12 worksheet tabs.

TIA

Gerry
 
Give more details of what you have and what you want to achieve.

Pete





- Show quoted text -
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
Give more details of what you have and what you want to achieve.

Pete
Click to expand...
Hi Pete

What I have in this case is three worksheets, named; Total, 101Finance
and Rental_AllCan. I have named a range in each worksheet to
correspond with the worksheet tab name. On worksheet Total there's a
range B4:Z126 named Total, on 101Finance there's a range B4:Z179 named
_101Finance and so on. After Rental_AllCan worksheet there is a
worksheet named IncStmt.

I may add or delete worksheets between Total and IncStmt and I am
trying to find a way so that I do not have to keep rewriting the macro
each time a worksheet is added or deleted.

This is what I currently have.

Selection.Consolidate Sources:=Array( _
"Total", _
"_101Finance", _
"StarlingSt",_
"Rental_AllCan"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

I have 12 different companies with a different macro for each because
they all have different worksheets(dept names). I was hoping to write
one macro for all. Like I said in the previous post, there may only
be the Total page and other companies could have up to 12 worksheets.

If it's still not clear I can upload a partial workbook.

Thanks again

Gerry
 
Hi Pete

What I have in this case is three worksheets, named; Total, 101Finance
and Rental_AllCan.  I have named a range in each worksheet to
correspond with the worksheet tab name.  On worksheet Total there's a
range B4:Z126 named Total, on 101Finance there's a range B4:Z179 named
_101Finance and so on.  After Rental_AllCan worksheet there is a
worksheet named IncStmt.

I may add or delete worksheets between Total and IncStmt and I am
trying to find a way so that I do not have to keep rewriting the macro
each time a worksheet is added or deleted.

This is what I currently have.

Selection.Consolidate Sources:=Array( _
        "Total", _
        "_101Finance", _
        "StarlingSt",_
        "Rental_AllCan"), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

 I have 12 different companies with a different macro for each because
they all have different worksheets(dept names).  I was hoping to write
one macro for all.  Like I said in the previous post, there may only
be the Total page and other companies could have up to 12 worksheets.

If it's still not clear I can upload a partial workbook.

Thanks again

Gerry
Click to expand...

Try this idea or send me the file as I suggested earlier

option explicit
Sub midsheets()
dim i as long
For i = 2 To Sheets.Count - 1
MsgBox Sheets(i).Name
Next i
End Sub
 
Back
Top