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.


--
babycody
------------------------------------------------------------------------
babycody's Profile: http://www.excelforum.com/member.php...o&userid=13120
View this thread: http://www.excelforum.com/showthread...hreadid=389104

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" wrote in message
...

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.


--
babycody
------------------------------------------------------------------------
babycody's Profile: http://www.excelforum.com/member.php...o&userid=13120
View this thread: http://www.excelforum.com/showthread...hreadid=389104

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

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

Jim Cone
San Francisco, USA


"babycody"

wrote inmessage
...

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