Redefining a table range with a macro
 

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

Redefining a table range with a macro
 

On May 21, 4:57*pm, OMER 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

Redefining a table range with a macro
 

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

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

Redefining a table range with a macro
 

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

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

.

Redefining a table range with a macro
 

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 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
.

Redefining a table range with a macro
 

Something like this
sub create5tables()
NumRows = 3
NumCols = 6
for i=1 to 5 step 8
cells(i,1).resize(numrows,numcols).name="table"&i
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

.

Redefining a table range with a macro
 

Don,
This is how I finally solved it:

ShtName = ActiveSheet.Name
ActiveWorkbook.Names("Table1").Delete
With Sheets(ShtName)
Set LastCell = .Cells(ColSize, RowSize)
TableRange = .Range(.Range("AB3"),
LastCell).Address(ReferenceStyle:=xlR1C1, external:=True)
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" &
TableRange
End With

Regards,
OMER
"Don Guillett" wrote:

Something like this
sub create5tables()
NumRows = 3
NumCols = 6
for i=1 to 5 step 8
cells(i,1).resize(numrows,numcols).name="table"&i
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

.


.

Redefining a table range with a macro
 

If you like it ............... However, what happens with the other 4
tables???

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
Don,
This is how I finally solved it:

ShtName = ActiveSheet.Name
ActiveWorkbook.Names("Table1").Delete
With Sheets(ShtName)
Set LastCell = .Cells(ColSize, RowSize)
TableRange = .Range(.Range("AB3"),
LastCell).Address(ReferenceStyle:=xlR1C1, external:=True)
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" &
TableRange
End With

Regards,
OMER
"Don Guillett" wrote:

Something like this
sub create5tables()
NumRows = 3
NumCols = 6
for i=1 to 5 step 8
cells(i,1).resize(numrows,numcols).name="table"&i
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

.


.

Redefining a table range with a macro
 

If desired, send your file to my address below. 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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If you like it ............... However, what happens with the other 4
tables???

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
Don,
This is how I finally solved it:

ShtName = ActiveSheet.Name
ActiveWorkbook.Names("Table1").Delete
With Sheets(ShtName)
Set LastCell = .Cells(ColSize, RowSize)
TableRange = .Range(.Range("AB3"),
LastCell).Address(ReferenceStyle:=xlR1C1, external:=True)
ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" &
TableRange
End With

Regards,
OMER
"Don Guillett" wrote:

Something like this
sub create5tables()
NumRows = 3
NumCols = 6
for i=1 to 5 step 8
cells(i,1).resize(numrows,numcols).name="table"&i
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

.


.