Excell Automation... how to select correct range for a pivot table.

>>So my code opens the workbook but it selects a fixed range.
can you tell us how the fixed range was being selected? via programming or it was manually set in the Excel file?

Hi Ryan Chong
Here is part of the code I am copying from an excell macro

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "a04!R1C1:R19C32", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    Sheets("Sheet4").Select

As you can see the macro puts a fixed range.....
Thank you

so i guess the part:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "a04!R1C1:R19C32", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion10

need to be updated? if yes, you can try use the the line below to get the last Row of the worksheet so that it can be placed later into the code above, try this:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "a04!R1C1:R19C" & LastRow, Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    Sheets("Sheet4").Select

Select allOpen in new window

see if this works?

Ryan,
Sorry my visual basic 2013 source does not recognize commads like CELLS,RANGE, XLUP...etc.
Do i need a special imports ?
What are you using ?
Can you send me a complete example of your test source
Regards

Thank you  very much!!

Works perfect!