Hi Members,

Greeting to everyone. I am new to this group and this is my first
posting.

I have multiple sheets (about 10 - sheet names end with a number) in
my workbook.
Each sheet has a different number of filled in rows and columns and
these sheets are subject to frequent revision. I want to assign the
sheet tab names to the active cells in each sheet and I will be using
these named ranges in my pivot tables.

If I define the names manually (using menu Insert -- Name--
Define... ), then every time I need to change the named area for each
sheet. Its taking lot of time for me.

Is there any smart way of defining the names using formulas?
If not, can some of you guide me on the VBA codes for this? ( I am
beginner to VBA programming).

Thanks in advance,
Ramesh.

On 3 Aug, 07:15, wrote:
Hi Members,

Greeting to everyone. I am new to this group and this is my first
posting.

I have multiple sheets (about 10 - sheet names end with a number) in
my workbook.
Each sheet has a different number of filled in rows and columns and
these sheets are subject to frequent revision. I want to assign the
sheet tab names to the active cells in each sheet and I will be using
these named ranges in my pivot tables.

If I define the names manually (using menu Insert -- Name--
Define... ), then every time I need to change the named area for each
sheet. Its taking lot of time for me.

Is there any smart way of defining the names using formulas?
If not, can some of you guide me on the VBA codes for this? ( I am
beginner to VBA programming).

Thanks in advance,
Ramesh.

Not sure I understand all of your requirements (especially the sheet
names part) but on a cursory read I think you would be greatly helped
by reading about dynamic range names which is basically a "smart way
of defining the names using formulas" as you state.

Check out:
http://www.beyondtechnology.com/geeks007.shtml
http://www.ozgrid.com/Excel/DynamicRanges.htm

Chrisso

On Aug 3, 12:54 pm, Chrisso wrote:
On 3 Aug, 07:15, wrote:





Hi Members,

Greeting to everyone. I am new to this group and this is my first
posting.

I have multiple sheets (about 10 - sheet names end with a number) in
my workbook.
Each sheet has a different number of filled in rows and columns and
these sheets are subject to frequent revision. I want to assign the
sheet tab names to the active cells in each sheet and I will be using
these named ranges in my pivot tables.

If I define the names manually (using menu Insert -- Name--
Define... ), then every time I need to change the named area for each
sheet. Its taking lot of time for me.

Is there any smart way of defining the names using formulas?
If not, can some of you guide me on the VBA codes for this? ( I am
beginner to VBA programming).

Thanks in advance,
Ramesh.

Not sure I understand all of your requirements (especially the sheet
names part) but on a cursory read I think you would be greatly helped
by reading about dynamic range names which is basically a "smart way
of defining the names using formulas" as you state.

Check out:
http://www.beyondtechnology.com/geeks007.shtml
http://www.ozgrid.com/Excel/DynamicRanges.htm

Chrisso- Hide quoted text -

- Show quoted text -

Hi Chrisso,

Thanks very much for providing those links. I liked the first one very
much (visited the second one earlier as well- 1st one is more clear
and elaborate).

I got a solution for my requirement now.

Thanks again.

Cheers,
Ramesh