Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA and Pivot tables (Excel 2003)

    Hi

    I want to be able to select a line of text from a pivot table, copy it and then use it as the selection criteria in a filter as part of a vba macro but i am getting confused,

    I am thinking that I need to creat a variable as being text so I went with dim variable as string, but I can't see how to use it as the search criteria.

    hope you can help cheers

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

    Re: VBA and Pivot tables (Excel 2003)

    Please provide some details about the code you have, and about the data.

  3. #3
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA and Pivot tables (Excel 2003)

    Hi Hans

    The code which I recorded and tried to manipulate afterwards for what its worth is

    Sub Macro2()
    Static jchse As String
    jchse = Selection.Copy
    Sheets("Data").Select
    Selection.AutoFilter Field:=11, Criteria1:=jchse, Operator:=xlAnd
    Range("Y11932").Select
    End Sub

    The data is accounting in nature, and replicates the contents of our ledgers, I am using a pivot to look at specific items within codes and cost centres, I wanted to use the macro to then look at the data (on a seperate sheet) and just filter on the selected description to see which other codes were also affected by a journal between codes etc.

    cheers

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

    Re: VBA and Pivot tables (Excel 2003)

    How do you want to specify the criteria? Should the user select a cell before running the macro, or will the vlaue you want to filter on always be in a specific cell?

  5. #5
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA and Pivot tables (Excel 2003)

    Hi Hans

    I anticipate the user, selecting the cell themselves with a single click and then running the macro, rather than it being a specific posistion each time,

    cheers

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

    Re: VBA and Pivot tables (Excel 2003)

    Try this:

    Sub Macro2()
    Dim jchse As String
    jchse = Selection.Value
    Sheets("Data").Select
    Selection.AutoFilter Field:=11, Criteria1:=jchse
    End Sub

    If that doesn't work, could you post a stripped down copy of the workbook? Remove unnecessary data, and delete or alter sensitive data.

  7. #7
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA and Pivot tables (Excel 2003)

    youre a proper full blown genius - thanks very much

Posting Permissions

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