Dynamic named range across multiple sheets

I know how to make a dynamic named range for a single sheet. I can't find any information on how to extend the named range to the last sheet in the workbook. I am importing a delimited text file into Excel, and the size varies. I am using vba to automatically add sheets when needed. That causes me to have a fluctuating amount of sheets. Is there a formula for a dynamic named range that would go to the last used cell in the last sheet without specifying the sheet name? Is it even possible to have a dynamic named range that extends into another sheet? Thanks for any help you offer.

bc,

If the name of the first sheet in the workbook is "StartSheet"
and the name of the last sheet is "EndSheet" then the following
named range formula applies to all sheets in the workbook.
New sheets should be added between the first/last sheets...

=StartSheet:EndSheet!$C$5:$C$16

Jim Cone
San Francisco, USA


"babycody" <[email protected]> wrote in message
news:[email protected]...

I know how to make a dynamic named range for a single sheet. I can't
find any information on how to extend the named range to the last sheet
in the workbook. I am importing a delimited text file into Excel, and
the size varies. I am using vba to automatically add sheets when
needed. That causes me to have a fluctuating amount of sheets. Is there
a formula for a dynamic named range that would go to the last used cell
in the last sheet *without specifying the sheet name*? Is it even
possible to have a dynamic named range that extends into another sheet?
Thanks for any help you offer.
--
babycody

That's kind of what I am looking for. However I never know the name of the last sheet. New sheets are created when the number of items I am importing from the text file excedes Excel's row limit per sheet of 65536. I was wondering if there was a way to leave this open so that the formula automatically finds the last sheet.

If it was me, I would change the import macro so that each new sheets is
added just before the last sheet...

Worksheets.Add Before:=Sheets(Sheets.Count), Count:=1

Jim Cone
San Francisco, USA


"babycody"
<[email protected]>
wrote inmessage
news:[email protected]...

That's kind of what I am looking for. However I never know the name of
the last sheet. New sheets are created when the number of items I am
importing from the text file excedes Excel's row limit per sheet of
65536. I was wondering if there was a way to leave this open so that
the formula automatically finds the last sheet.
--
babycody