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

On Jul 14, 4:30*pm, Wally <gerrywald...@gmail.com> wrote:
> 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

On Jul 14, 11:28*am, Pete_UK <pashu...@auditel.net> wrote:
> Give more details of what you have and what you want to achieve.
>
> Pete
>
> On Jul 14, 4:30*pm, Wally <gerrywald...@gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - 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."

On Jul 14, 12:28*pm, Pete_UK <pashu...@auditel.net> wrote:
> 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

On Jul 14, 1:05*pm, Wally <gerrywald...@gmail.com> wrote:
> On Jul 14, 12:28*pm, Pete_UK <pashu...@auditel.net> wrote:> 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

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