0

Hope anyone could point out my issues with my current code as my code could not detect the colour formatted cells and delete the other columns that do not have colour formatted cells.

What I have created is a pivot table (PT1) from a consolidated sheet, and I have also applied certain conditional formatting. After the conditional formatting, some cells have a red background colour. What I want to achieve now, is to copy the entire sheet of PT1 to another worksheet within the same workbook, and delete the columns that do not have colour formatted cells.

Sub Filter()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim opensheet As Worksheet
Dim Filterlastcol As Integer, Filterlastrow As Integer
Dim Pivotprefix As String
Dim FilterPivRng As Range, c As Range
Dim i As Long, j As Long, targetfilter As Long

  With Application
  .ScreenUpdating = False
  .EnableEvents = False
  End With

'Delete the filter pivot table if it exists
  Application.DisplayAlerts = False
  On Error Resume Next
  For Each opensheet In Application.Worksheets

  Pivotprefix = Left(opensheet.Name, 4)

  If Pivotprefix = "Filt" Then
      opensheet.Delete
  End If

Next opensheet

'select and copy entire sheet that has pivot table and paste to new sheet     
  Sheets("Pivot_Table (Name)").Select
  Cells.Select
  Selection.Copy
  Set DestSh = ActiveWorkbook.Worksheets.Add
  DestSh.Name = "Filter Pivot Table"
  Range("A1").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False

'Determine my entire range of pivot table (does not include row header
 or column header)

    Range("B5").Select

    Do Until IsEmpty(ActiveCell)

        Filterlastcol = ActiveCell.Column

        ActiveCell.Offset(0, 1).Select
    Loop

    Range("B5").Select

    Do Until IsEmpty(ActiveCell)

        Filterlastrow = ActiveCell.Row

        ActiveCell.Offset(1, 0).Select
    Loop

    'set my range without the last row (grandtotal), hence row = filterlastrow -1

     Set FilterPivRng = Range(Cells(5, 2), Cells(Filterlastrow - 1, Filterlastcol))

        For i = 1 To FilterPivRng.Rows.Count
            For j = 1 To FilterPivRng.Columns.Count

                targetfilter = FilterPivRng.Cells(i, j).Interior.Color

           If targetfilter <> rgbRed Then
            Cells(i, j).EntireColumn.Delete
            End If

        Next j
    Next i

  End Sub

Have I done something wrong at the last part of codes? I'm not very familiar in colour formatting cells. Surprisingly the code could run, but nothing seem to happen at the very end. When I click F8 to see the breakdown, everything is fine at the front, until it reaches the delete column part.

*Edit

*The main reason why I am doing deletion columns of pivot table to narrow down my range is because, my date field could not be filtered as it appears as columns. The dates do not appear in just one column, it's a range of columns across a same row of a specific date range ("Sum 1/9/16", "Sum 2/9/16",etc..). That is the reason why I copied the pivot table into another sheet as a duplicate, to narrow down my colour formatted cells with respect to column and row, for a better presentation.

Thanks for your time and understanding. Thanks in advance!

Skyler
  • 49
  • 6
  • A pivot table is merely a visual representation of the underlying data. So, if you want to change what is shown in the pivot table then you'll have to change the underlying table / data. Alternatively, you can put a filter on the pivot table and filter the values you do not want to see. In that case they are not deleted but merely not shown. The same applies to entire columns. You can hide a column in the pivot table. But that does not delete the underlying data. It is just not showing. – Ralph Oct 03 '16 at 09:24
  • Thanks for your response Ralph. I am aware of the use of pivot table, however my pivot table is too complex whereby I have multiple column headers (individual dates in a range), and multiple row headers (names), and sub row headers (name->role->etc..). That's the reason why I have to paste the entire PT1 into another sheet, so that I can do the filter. I cannot seem to find a method to do a filter using vba across multiple columns under the same condition. Makes sense? – Skyler Oct 03 '16 at 11:10
  • Not to me. But maybe someone else understands and can help you. Anyway, if you understand that you cannot `delete` in a pivot table but merely filter then I don't understand why you are using `delete` in your VBA code. If you want to (learn how to) filter a pivot tables then there are many good examples on StackOverflow: http://stackoverflow.com/questions/11071662/filter-excel-pivot-table-using-vba OR simply look what better suits your needs http://stackoverflow.com/search?q=excel+vba+filter+pivot+table – Ralph Oct 03 '16 at 11:14
  • Okay, ignore the part of me saying deleting. Do you happen to know how to do one filter across multiple data fields (ie. multiple date column headers ("Sum of 1/9/16", "Sum of 2/9/16", etc...)? Most of the filters are normally set in one data field (ie. "Date"). In my case, I cannot pre-summarize all dates and present as a data field, I had to present all dates in individual columns. – Skyler Oct 03 '16 at 11:52
  • Interior.color doesn't return the color set via a conditional formatting, you should test using the condition in the conditional formating. – h2so4 Oct 03 '16 at 12:03

0 Answers0