Prev: Get control of a Userform by the Name
Next: get Control of a Butoon placed in the Sheet
From: OMER on 21 May 2010 07:57
Hello,
I need help creating code to redefine a table within a macro. I used the
macro recorder to creata a table with the same name but to point to another
locations.

ActiveWorkbook.Names("Table1").Delete
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _
"='Sheet1'!R3C28:R12C37"

How do I make this dynamic, so the starting point, #of rows and # of columns
are defined by variables?

Help is much appreciated.
Regards,
OMER
From: Joe on 21 May 2010 08:06
On May 21, 4:57 pm, OMER <O...(a)discussions.microsoft.com> wrote:
> Hello,
> I need help creating code to redefine a table within a macro. I used the
> macro recorder to creata a table with the same name but to point to another
> locations.
>
>     ActiveWorkbook.Names("Table1").Delete
>     ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _
>         "='Sheet1'!R3C28:R12C37"
>
> How do I make this dynamic, so the starting point, #of rows and # of columns
> are defined by variables?
>
> Help is much appreciated.
> Regards,
> OMER

sorry if I assumed too much!

if its just to resize, you can use a dynamic range
In excel define a named range with following formula.

Named_Range = OFFSET('Sheet1'!$A$1,1,0,COUNTA('Sheet1'!$A:$A),5)

HTH
Joe
From: Don Guillett on 21 May 2010 08:16

If you don't want to use a self adjusting defined name range as suggested
tell us how to determine the variables........
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"OMER" <OMER(a)discussions.microsoft.com> wrote in message
news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com...
> Hello,
> I need help creating code to redefine a table within a macro. I used the
> macro recorder to creata a table with the same name but to point to
> another
> locations.
>
> ActiveWorkbook.Names("Table1").Delete
> ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _
> "='Sheet1'!R3C28:R12C37"
>
> How do I make this dynamic, so the starting point, #of rows and # of
> columns
> are defined by variables?
>
> Help is much appreciated.
> Regards,
> OMER

From: OMER on 22 May 2010 13:39
Thank you both for your quick response.
Actually I need to create 5 tables. Each will have the same size as the
others. The starting cell for each of them is fixed.
Let's say the following is given:
NumRows = 'this is Variable
NumCols = 'this is Variable
Starting Cell = ' this is fixed for each table. For example: AA1, BA1,
CA1, etc.

Resizing them is not working as expected, so I want to delete them and then
create them again using the new size (determined by NumRows and NumCols).

All this within a macro.

Thank youfor your help.

"Don Guillett" wrote:

>
> If you don't want to use a self adjusting defined name range as suggested
> tell us how to determine the variables........
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "OMER" <OMER(a)discussions.microsoft.com> wrote in message
> news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com...
> > Hello,
> > I need help creating code to redefine a table within a macro. I used the
> > macro recorder to creata a table with the same name but to point to
> > another
> > locations.
> >
> > ActiveWorkbook.Names("Table1").Delete
> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _
> > "='Sheet1'!R3C28:R12C37"
> >
> > How do I make this dynamic, so the starting point, #of rows and # of
> > columns
> > are defined by variables?
> >
> > Help is much appreciated.
> > Regards,
> > OMER
>
> .
>
From: OMER on 22 May 2010 13:40
Thank you both for your quick response.
Actually I need to create 5 tables. Each will have the same size as the
others. The starting cell for each of them is fixed.
Let's say the following is given:
NumRows = 'this is Variable
NumCols = 'this is Variable
Starting Cell = ' this is fixed for each table. For example: AA1, BA1,
CA1, etc.

Resizing them is not working as expected, so I want to delete them and then
create them again using the new size (determined by NumRows and NumCols).

All this within a macro.

Thank you for your help.


"Joe" wrote:

> On May 21, 4:57 pm, OMER <O...(a)discussions.microsoft.com> wrote:
> > Hello,
> > I need help creating code to redefine a table within a macro. I used the
> > macro recorder to creata a table with the same name but to point to another
> > locations.
> >
> > ActiveWorkbook.Names("Table1").Delete
> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _
> > "='Sheet1'!R3C28:R12C37"
> >
> > How do I make this dynamic, so the starting point, #of rows and # of columns
> > are defined by variables?
> >
> > Help is much appreciated.
> > Regards,
> > OMER
>
> sorry if I assumed too much!
>
> if its just to resize, you can use a dynamic range
> In excel define a named range with following formula.
>
> Named_Range = OFFSET('Sheet1'!$A$1,1,0,COUNTA('Sheet1'!$A:$A),5)
>
> HTH
> Joe
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Get control of a Userform by the Name
Next: get Control of a Butoon placed in the Sheet