Build table in 2010

[Blackie50]

Hi,

I recorded the following macro in excel 2010....


ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$87"), , xlYes).Name = _
"Table1"

but now realise that the size changes each day. Please could some one help with code to build a table with a changing number of rows

many thanks
Jon

[mancubus]

is it



ActiveSheet.ListObjects.Add(xlSrcRange, Range("tbl"), , xlYes).Name = "Table1"


tbl is defined range create via following formula:


=OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))

data is the worksheet name that contains table data

[Blackie50]

Thanks for reply but unfortunately don't understand.

I assume you replace the 1st bit of code with what was recorded but
how is the 'data' bit stored and where?

regards
Jon

[Kenneth Hobs]

Do the 2nd part first.


tbl is defined range create via following formula This means that when you create the named range tbl, then add the following formula that was posted. This is called a dynamic named range.


data is the worksheet name that contains table data Data is the worksheet name. Typically, that might be Sheet1.

[Blackie50]

Thanks for reply - but add the formula where?

regards

[mancubus]

"data" is a sample worksheet name where your table is and should be changed to your actual worksheet name.


Named Range:
ribbon, formulas, define name, define name (or ribbon, formulas, name manager, new)
name: tbl (or whatever you like)
scope: (up to you, workbook or...)
refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
($A$1 means range's (or table's) top left cell is A1)

by a dynamic range any added columns and rows are covered without havig to change the range in the code or formulas.



actually, if it is an xl Table, new rows and columns are added to the Table automatically.

[Kenneth Hobs]

Add the formula in the Refers To: in 2010's Formulas > Name Manager > New > tbl > in Refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) > OK.

[Blackie50]

Thanks for reply,

Not sure I can do this

The macro(s) are held in a separate spreadsheet - first thing it does is pick up the new days text file (which has all the numbers etc) and changes it in to a spreadsheet. I can rename the text file to 'data' so that when it creates the spreadsheet its called 'data' (and change the code so it picks the text file called data.

But I can't make the changes to the sheet called 'data' everytime - can I do all the fancy stuff in the code by using the macro recorder?

cheers

[Kenneth Hobs]

ActiveSheet.ListObjects.Add(xlSrcRange, _
Range("A1:F" & Range("F" & Rows.Count).End(xlUp).Row), _
, xlYes).Name = "Table1"

[Blackie50]

Thanks Kenneth,

Just what I was looking for - worls like a charm

regards
Jon