hi there guys ang gals

i have a problem with a pivot table i have recorded the following code with the macro recorder and it works fine, but it only works with the file "et-0171" how can i make it work for any file and any range(depth of rows)??

thanks for your time.



Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'et-0171'!R1C11:R12004C12").CreatePivotTable TableDestination:= _
        "'[et-0171.xls]et-0171'!R1C13", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
       ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With

Hi Thelos

Dim myname As String
myname = ActiveWorkbook.Name
Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'et-0171'!R1C11:R" & Range("L65000").End(xlUp).Row & "C12").CreatePivotTable TableDestination:= _
        "'[" & myname & ".xls]et-0171'!R1C13", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
       ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With


Cheers

Matt

Hi matt

do i need to replace all references to et-0171 and replace them with myname??
i tried the amended code but it is throwing up a run time error 5

thanks for your time man!!

Hi Thelos

It depends if the sheet name is consistantly et-0171. If it is then you don't have to do anything, if not, then you must replace the code to something like the following...

Dim myWBook As String
Dim myWSheet As String
myWBook = ActiveWorkbook.Name
myWSheet = ActiveSheet.Name
Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'" & myWSheet & "'!R1C11:R" & Range("L65000").End(xlUp).Row & "C12").CreatePivotTable TableDestination:= _
        "'[" & myWBook & ".xls]" & myWSheet & "'!R1C13", TableName:="PivotTable2"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
     ActiveSheet.PivotTables("PivotTable2").AddDa taField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With

hi there mjppaba,

i should have said it is to be used for a number of differently named sheets, but all the information i want is always in columns k&l.
i am relativley new to the vb as you might of been able to tell from the amount of posts i have thanks for the time mjppaba.