| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Redefining a Named Range In A Loop | Excel Programming | |||
| Redefining pivot table source | Excel Programming | |||
| Redefining a new Range area - Thanks Nick Hodge and Norman | Excel Programming | |||
| Redefining a new Range area | Excel Programming | |||
| Redefining Styles | Excel Programming | |||
Hybrid Mode
