Macro for Creating Tables in Excel 2007
 

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with


"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

Hi,

Another way to set the range is:

Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

Then used the variable SelectionAddress to set the address.

David

"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

David: If there are blank cells in column A your method may not work.
curtrent area stops when it finds a blank cell in the first row or fist
column.

"David" wrote:

Hi,

Another way to set the range is:

Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

Then used the variable SelectionAddress to set the address.

David

"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

I like this instead of .ListObjects.Add code line
No problems if the Table already exist

Application.CommandBars.ExecuteMso "TableInsertExcel"


I use it it this Table Tools add-in
http://www.rondebruin.nl/table.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joel" wrote in message ...
Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with


"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

Hi,
Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
range("A1").select
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

As long as you are somewhere in the table, this will work.
range("A1").select will make sure you are in the table. Blanks will not
matter,

David

"Joel" wrote:

David: If there are blank cells in column A your method may not work.
curtrent area stops when it finds a blank cell in the first row or fist
column.

"David" wrote:

Hi,

Another way to set the range is:

Sub Macro1()
' Macro1 Macro
' Macro recorded 1/23/2009
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address
End Sub

Then used the variable SelectionAddress to set the address.

David

"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

I tied what you recomended but it only turn the first column into table, the
rest remain the same...



"Ron de Bruin" wrote:

I like this instead of .ListObjects.Add code line
No problems if the Table already exist

Application.CommandBars.ExecuteMso "TableInsertExcel"


I use it it this Table Tools add-in
http://www.rondebruin.nl/table.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joel" wrote in message ...
Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with


"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

Excel guess what the range is if you not select it first
If you have a empty column or row in the range it stop

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"MESTRELLA29" wrote in message ...
I tied what you recomended but it only turn the first column into table, the
rest remain the same...



"Ron de Bruin" wrote:

I like this instead of .ListObjects.Add code line
No problems if the Table already exist

Application.CommandBars.ExecuteMso "TableInsertExcel"


I use it it this Table Tools add-in
http://www.rondebruin.nl/table.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joel" wrote in message ...
Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with


"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"

Macro for Creating Tables in Excel 2007
 

Just wanted to let you know what finally work for me, Thanks for your help.

Range("A1").Select
Selection.CurrentRegion.Select
SelectionAddress = Selection.Address

Application.CommandBars.ExecuteMso "TableInsertExcel"



"Ron de Bruin" wrote:

Excel guess what the range is if you not select it first
If you have a empty column or row in the range it stop

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"MESTRELLA29" wrote in message ...
I tied what you recomended but it only turn the first column into table, the
rest remain the same...



"Ron de Bruin" wrote:

I like this instead of .ListObjects.Add code line
No problems if the Table already exist

Application.CommandBars.ExecuteMso "TableInsertExcel"


I use it it this Table Tools add-in
http://www.rondebruin.nl/table.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joel" wrote in message ...
Avoid using the select method

with ActiveSheet
set LastRow = .Range("A1").end(xldown)
set LastCell = LastRow.end(xltoleft)
set DataRange = .Range("A1",LastCell)

.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "Table1"
DataRange.ListObjects("Table1").TableStyle = "TableStyleMedium18"
End with


"MESTRELLA29" wrote:

Hi to all, I am downlading a File form external data and want to turn this
automaticlly in to table in excel 2007, when I run the macro this takes only
the selected range, but my download is never the same Size.

Take a look at what i have and please help correct it

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$J$57"), ,
xlYes).Name = _
"Table1"
Range("A1:J57").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium18"