Consolidate by range names which represent worksheet tab names

[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

 

[Pete_UK]

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

Pete

 

[Don Guillett Excel MVP]

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

Pete





- Show quoted text -

"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."

 

[Wally]

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

Pete

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

 

[Don Guillett Excel MVP]

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

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