Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PIVOT TABLE WITH HIDDEN ROWS

    Hi there.
    I just can't seem to crack the hidden rows code totally. Below is the coding for a pivot table I first delete in sheet called "summary", then build one again in sheet called "detail" where my headings are in rows "a29 to u??" depended on the filter selection. I need to rebuilt the pivot table everytime as the ranges change all the time. When I filter data, the pivot table must only include the filtered data. You people helped me before with something similar, but because I am not a VB programmer I can't always fix my own problems.

    The line with the ** is giving me problems.

    Application.ScreenUpdating = False
    Range("A29").Select
    Sheets("SUMMARY").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("DETAIL").Select
    ** ActiveSheet.Range("a29":u??, ActiveSheet.Range("a29"u??").End(x1down)).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "DETAIL!R29C1:R184C91", TableDestination:=Range("SUMMARY!A1"), TableName:="PivotTable2"
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="SHORT MODEL", _
    PageFields:="NAAMSA RETAIL MONTH"
    ActiveSheet.PivotTables("PivotTable2").PivotFields ("NAAMSA RETAIL MONTH"). _
    Orientation = xlDataField
    Sheets("DETAIL").Select
    Range("A29").Select
    Sheets("SUMMARY").Select
    Application.ScreenUpdating = True
    End Sub

    I attach herewith a part of my workbook (all the command buttons "summary...." needs to only include the filtered data in the pivot tables.

    Please help again.

    Thanks a lot

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    I think the best way to deal with filtered data and making a pivot table from the filtered list is to make a macro that first copies the filtered list to a temporary spreadsheet and then make a pivot table from it, using the Currentregion property from the range object.
    I don't think you can use wildcard characters like * or ? in the range address.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    Sorry, my file was too big to attach. Herewith an example of what I described above. Thanks Hans for info, if there is no other way out, I will have to follw your advice.
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    Rene,

    Have you considered adding a Page Field to you Pivot Table. Doing that would allow you to actually filter the Pivot Table in effect.

    I am attaching a copy of you file amended to show this.

    Let me know if that is adequate for your needs.

    Andrew
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    Hi Andrew,
    In my actual file I have already got a Month as a pivot filter, that is why I need the the other data filtered in the spreadsheet. Sorry, I should have given you a proper example.

    Thanks again

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    Just for the record, you can have more than one page field, so that you could filter by Month and whatever other field(s) you require.

    Does that assist ?

    Andrew

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PIVOT TABLE WITH HIDDEN ROWS

    OOPS!! I didn't know that. Yes, this is perfect, thank you.

Posting Permissions

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