locked
Pivot table with macro RRS feed

  • Question

  • I'm trying to write a macro that can create pivot tables. I can get it working fine for one file, however, I have many different files with different file names. I was wondering if there was any way to make this file run independent of the file name

    Here's the code:

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "'file_name '!R1C15:R1500C28").CreatePivotTable _
            TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
        ActiveSheet.Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Pivot")
            .Orientation = xlRowField
            .Position = 1
        End With

     

    Basically, if I could just have the macro always select the source data from the current worksheet, that would be great.

     

    Thursday, July 22, 2010 11:16 PM

Answers

  • You can use the Activesheet object.

    "'" & Activesheet.name & "'!R1C15:R1500C28").CreatePivotTable _


    Cheers   www.andypope.info
    • Marked as answer by excel_newbie Monday, July 26, 2010 9:48 AM
    Friday, July 23, 2010 7:48 AM