Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Woes (Excel 2003 - 3)

    I am having a problem creating a pivot table using a variable to get the name of the file. Here is the code:

    Set wb1 = activeworkbook

    Now is the confusing part. The first set of lines works fine, but it uses the actual name of the file:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "Tickets").CreatePivotTable TableDestination:= _
    "'[Copy of July Service Report Data_J.xls]Pivot Tables'!R3C1", TableName:= _
    "IBD Table", DefaultVersion:=xlPivotTableVersion10

    However, this code fails each time and I don't know why. The only difference is that I am using a variable in place of the actual filename. Any ideas?

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Tickets").CreatePivotTable TableDestination:= _
    "'[" & wb1.Name & "]Pivot Tables'!R3C1", TableName:="IBD Table", DefaultVersion:= _
    xlPivotTableVersion10

    As always, my grateful thanks for all the help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Woes (Excel 2003 - 3)

    Try

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Tickets").CreatePivotTable TableDestination:= _
    Worksheets("Pivot Tables").Range("A3"), TableName:="IBD Table", DefaultVersion:= _
    xlPivotTableVersion10

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Woes (Excel 2003 - 3)

    Hi Hans,

    Tried your version, but it did not work either. I get Run-time error 438, "Object doesn't support this property or method."

    Very vexing...

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Woes (Excel 2003 - 3)

    I finally got it! Here is what finally worked for me:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Tickets").CreatePivotTable TableDestination:= _
    "'[" & wb1.name & "]PivotTables'!R3C1",TableName:="IBD Table", DefaultVersion:= _
    xlPivotTableVersion10

    Thank you for your efforts and for making me focus...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •