Pivot Table in VB

[Alastair79]

I have a macro that changes an EDI file into a nice four column table, this
table changes in length according to the orders. So I do the following to
highlight all the data:

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

I then want to create a pivot table from the highlighted data. I recorded
myself doing this but as you can see it's given me a deffinate range of cells
instead of calling the highlighted cells:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R2C1:R479C4").CreatePivotTable TableDestination:=ActiveCell.Offset(0,
4) _
..Range("A1"), TableName:="PivotTable1"

Is there anyway around this? Thanks......... Alastair.

[pianoman]

Hi,
Assuming you have nothing above or below the data you want to include, I have used the entire Columns in my ranges before... this works fine, and has the advantage of including any more data you add. Just replace the RxCx bits with $D:$G or whatever the columns are...

hope this helps
gareth