Hi,

I am having issues trying to do a macro that will open a workbook and then create a pivot table based on values found in "data" sheet. Basically I have to do pivot for 90 workbooks, and that is why I am trying to do this macro. Each workbook has different amount of data so I am using this formula
Code:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),27)
this macro works good but I have to open manually each of the workbooks , paste this formula and then run the macro :S
Code:
Sub PivotCountries()
'
' PivotCountries Macro
'
' Keyboard Shortcut: Ctrl+t
'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DynamicRange", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet2!R2C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet2").Select
    Cells(2, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Contract Number")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Service Level")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Serial Number")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Install Site Region")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarter")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Maint Net Price"), "Count of Maint Net Price", _
        xlCount
    Range("D8").Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Count of Maint Net Price")
        .NumberFormat = "[$$-409]#,##0"
    End With
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "PIVOT"
    Range("A1").Select
    Sheets("Data").Select
End Sub
A