Pivot table
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Pivot table

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello,

    Sub Macro1()
    Range("F2").Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "MARTIN!R2C1:R155C8"
    End Sub

    Above stands a part of a macro to create a pivot table. With the following I have a problem:
    SourceData:="MARTIN!R2C1:R155C8"

    If the sheet has a name other then MARTIN I get an error.
    I want to play the macro whatever the sheet name is!
    How can I make that happem?!

    Best regards,

    Martin J.A. Maatman Oonk

  2. #2
    Guest

    Default

    If you want it to appear on the active sheet, use the following line instead of the one containing MARTIN!, you'll need to make sure of the range too though, otherwise you'll get an error: -

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "" & ActiveSheet.Name & "!" & "R2C1:R155C8"

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That was from me (so I take full responsibility ).

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Actually, thinking about it, the following should work for any sheet which has listed data in column A, (change the Range("A1").Select statement to reflect your data position): -

    Sub Macro1()
    Dim myRange as Range

    Range("A1").Select
    Set myRange = ActiveCell.CurrentRegion
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "" & ActiveSheet.Name & "!" & myRange.Address(ReferenceStyle:=xlR1C1) & ""
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com