Use List data to generate new list

If we could add the code we discussed to remove blank columns,
You could remove the columns you don't want from the results sheets on a one-time basis manually. They will remain hidden. The danger there is that one day, data you're unaware of might be there but remain in a hidden column.
They could be added/removed dynamically at each update in the code. This would add overhead/time. You want to go thaty way?
 
You want to go that way?
Code:
Sub blah()
SheetNo = 1
Set SourceList = Sheets("MASTER").Range("A1:XZ1000")
For Each Sht In Sheets(Array("Area1", "Area2", "Area3", "Area4"))
  With Sht
    .UsedRange.Clear
    On Error Resume Next: .Names("Extract").Delete: On Error GoTo 0
    .Range("YC1:YC2") = Application.Transpose(Array("Area", "*Area" & SheetNo & "*"))
    SourceList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("YC1:YC2"), CopyToRange:=.Range("A1"), Unique:=False
    .Cells.EntireColumn.AutoFit
    For Each colm In .UsedRange.Offset(1).Resize(, 170).Columns  ' limited to 170 columns, you can adjust.
      If Application.WorksheetFunction.CountBlank(colm) = colm.Rows.Count Then colm.EntireColumn.Hidden = True Else colm.EntireColumn.Hidden = False
    Next colm
  End With
  SheetNo = SheetNo + 1
Next Sht
End Sub
It takes about about half a second to update the 4 sheets here.
 
Applying this now to my working document will report results soonish. The sample is looking good.

Not sure why doc showed 0 views. I've been getting auto-logged-off this site after every 20-30 mins of inactivity, and have had some local internet troubles periodically as well. Could have something to do with it I suppose.
 
You could remove the columns you don't want from the results sheets on a one-time basis manually. They will remain hidden. The danger there is that one day, data you're unaware of might be there but remain in a hidden column.
They could be added/removed dynamically at each update in the code. This would add overhead/time. You want to go thaty way?

For some reason when applying this code to the real workbook I'm working with, all my dependent sheets (Area1, Area2, Area3 etc) return blank. I double checked that I have unique headers, and the values match (that is, the Sheet name "Area1" matches the value name "Area1" in the column on the master sheet.)

What else would be causing everything to appear blank?

Here were the steps I followed:

1. Started new, blank workbook.
2. Copied headers I need over to Master and all other sheets.
3. Copied data I need over to Master sheet.
4. Input code and changed values to reflect the actual table data (Area1 becomes ActualArea1 on the code [View Code when right clicking the Master sheet.])
5. Applied some conditional formatting to Master sheet (only so far)
6. Went to ActualArea1 sheet to view data. None appears, although many columns are hidden.
 
Not sure if this is relevant, but my Areas are not actually Area1, Area2, etc. More like, Area1 is XBCDEFG, Area2 is CEDFRAG, Area3 is SLGDJ, etc...

Likewise, my tasknames are not numbered and sequential, but each are unique values. I'm not sure if either of these impact the code, as I'm not at all familiar with VBA.
 
Not sure if this is relevant, but my Areas are not actually Area1, Area2, etc. More like, Area1 is XBCDEFG, Area2 is CEDFRAG, Area3 is SLGDJ, etc...

Likewise, my tasknames are not numbered and sequential, but each are unique values. I'm not sure if either of these impact the code, as I'm not at all familiar with VBA.
Yes it does matter.
Are the sheet names exactly the same as the area names used in column E of the mastersheet throughout?

If so:
Code:
Sub blah()
Set SourceList = Sheets("MASTER").Range("A1:XZ1000")
For Each Sht In Sheets(Array("Area1g", "Area2k", "Area3w", "Area4a"))
  With Sht
    .UsedRange.Clear
    On Error Resume Next: .Names("Extract").Delete: On Error GoTo 0
    .Range("YC1:YC2") = Application.Transpose(Array("Area", "*" & Sht.Name & "*"))
    SourceList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("YC1:YC2"), CopyToRange:=.Range("A1"), Unique:=False
    .Cells.EntireColumn.AutoFit
    For Each colm In .UsedRange.Offset(1).Resize(, 170).Columns  ' limited to 170 columns, you can adjust.
      If Application.WorksheetFunction.CountBlank(colm) = colm.Rows.Count Then colm.EntireColumn.Hidden = True Else colm.EntireColumn.Hidden = False
    Next colm
  End With
Next Sht
End Sub
 
Last edited:
Also, ultimately working with 1000 rows and nearly 1000 columns. Not sure if that threw off any parameters. I played with your code a bit, changing:

Code:
.Range("[B]YC1:YC2[/B]")
to
Code:
.Range("[B]A1:A2[/B]")

and

Code:
CriteriaRange:=.Range("[B]YC1:YC2[/B]")
to
Code:
CriteriaRange:=.Range("[B]A1:A2[/B]")

The net result was that I got a column in A1 on all my sheets displaying ALL the Area values for all areas, not filtered out selectively, but all other columns remained hidden and no data populated outside of that column. If I remove the code we added to hide columns and go with the previous version, nothing changes.
 
Yes it does matter.
Are the sheet names exactly the same as the area names used in column E of the mastersheet throughout?

If so:
Code:
Sub blah()
Set SourceList = Sheets("MASTER").Range("A1:XZ1000")
For Each Sht In Sheets(Array("[B]Area1g", "Area2k", "Area3w", "Area4a[/B]"))
  With Sht
    .UsedRange.Clear
    On Error Resume Next: .Names("Extract").Delete: On Error GoTo 0
    .Range("YC1:YC2") = Application.Transpose(Array("Area", "*" & Sht.Name & "*"))
    SourceList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("YC1:YC2"), CopyToRange:=.Range("A1"), Unique:=False
    .Cells.EntireColumn.AutoFit
    For Each colm In .UsedRange.Offset(1).Resize(, 170).Columns  ' limited to 170 columns, you can adjust.
      If Application.WorksheetFunction.CountBlank(colm) = colm.Rows.Count Then colm.EntireColumn.Hidden = True Else colm.EntireColumn.Hidden = False
    Next colm
  End With
Next Sht
End Sub

Yes I made sure everything matches throughout. I bolded the area in your code where I ensured the names matched. Am I missing something?

When I said I'm not sure if it matters, I meant I'm not sure if having nonsequential data matters (ie, if the code was expecting the data to be incremental.) I understand that exact matching is essential.

The code above (copied from your last post) gets me somewhere: my sheets now are populated with data in columns A-G corresponding to the correct area.

--The task data is still bonkers: I'm getting headers for Area3 and Area4 for all sheets in addition to relevant data.--

Fixed: problem was the 170 column limit, I had about 700 working columns. Okay, will update soon.
 
Last edited:
[Solved]

Minor issues remain:

The code removes my filters on the master sheet when it runs, and the code removes conditional formatting which turns blank cells within the array black on my Area sheets. Any way to fix these? I've tried reapplying it, but it just undoes it all every time the code runs. I'm guessing we'll have to code in the formatting and headers, but again, I'm not familiar with the method here.

I'm marking the thread solved as the primary issue is resolved. Still need this to get the primary functionality I'm seeking.

EDIT: Hmmm, does not seem to want to let me mark as solved.
 
Last edited:
Also, ultimately working with 1000 rows and nearly 1000 columns. Not sure if that threw off any parameters. I played with your code a bit, changing:

Code:
.Range("[B]YC1:YC2[/B]")
to
Code:
.Range("[B]A1:A2[/B]")

and

Code:
CriteriaRange:=.Range("[B]YC1:YC2[/B]")
to
Code:
CriteriaRange:=.Range("[B]A1:A2[/B]")
If you do that you must also change the CopyToRange to something like .Range("C1"), otherwise the results will overwrite the criteria.
 
Yep, I was just troubleshooting... Ended up making the .Range and CriteriaRange AAA1:AAA2 as it's outside the range which will see use. Those problems are now fixed.

The only remaining issues are minor ones relating to Data Filtering and Conditional Formatting. I think we're seeing some lag between our responses, as I just saw your response to a post I wrote half an hour ago.
 
Minor issues remain:
The code removes my filters on the master sheet when it runs,
It removes filters from the MASTER sheet!! Or do you mean it ignores filters on the master sheet by including filtered out rows on the results sheets?


and the code removes conditional formatting which turns blank cells within the array black on my Area sheets.
record yourself a macro reapplying the conditional format on one of the results sheets and post it here, I'll tweak.


I'm guessing we'll have to code in the <snip> headers, but again, I'm not familiar with the method here.
Do the headers (row 1) not have their formatting carried over? Oh… hold on, do you mean the formatting in the leftmost 8 or so columns? If so record that formatting as well in the same recorded macro.


EDIT: Hmmm, does not seem to want to let me mark as solved.
You might need to use Chrome or some other internet browser.
 
It removes filters from the MASTER sheet!! Or do you mean it ignores filters on the master sheet by including filtered out rows on the results sheets?


record yourself a macro reapplying the conditional format on one of the results sheets and post it here, I'll tweak.


Do the headers (row 1) not have their formatting carried over? Oh… hold on, do you mean the formatting in the leftmost 8 or so columns? If so record that formatting as well in the same recorded macro.


You might need to use Chrome or some other internet browser.

YES it is removing my data filters from the Master sheet! (And all other sheets.)

I am using Chrome. I suspect it has to do with being a new member, and thus not being allowed to edit my original post.

Macro for formatting (want to apply to all sheets)

Code:
Sub Macro4()'
' Macro4 Macro
' Alternating rows and Black Blanks
'


'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(A1))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

The selection range for both formats was $A$2:$ZZ$1000

The other thing was data filters on headers. There's a lot of data, and I don't want my users to have to reapply a filter every time they want to look at the data presented. Ultimately it's just the KISS mentality for the end user.
 
Last edited:
YES it is removing my data filters from the Master sheet! (And all other sheets.)
It does indeed remove the Autofilter from the Master sheet. I didn't know this!
Testing here shows that the line which applies the Advanced Filter removes the Autofilter from the MASTER sheet.
Any filters that were present in the results sheets are removed because a result sheet is cleared out before updating it.
I couldn't duplicate autofilters removed from sheets in the same workbook that were not concerned - these remained.[/QUOTE]


The selection range for both formats was $A$2:$ZZ$1000
I will look at this later.


The other thing was data filters on headers. There's a lot of data, and I don't want my users to have to reapply a filter every time they want to look at the data presented. Ultimately it's just the KISS mentality for the end user.
Are the filters you want to apply to the MASTER seet before you update the results sheets always the same, or quite similar? I ask because we're using Advanced Filter criteria at a fairly basic level and we could refine the criteria to do the same filtering as you're doing on the MASTER sheet.
 
Are the filters you want to apply to the MASTER seet before you update the results sheets always the same, or quite similar? I ask because we're using Advanced Filter criteria at a fairly basic level and we could refine the criteria to do the same filtering as you're doing on the MASTER sheet.

It's mostly for ease of record editing that I want the Master sheet to be filtered. There will be shift changes, there will be tasks that need to be checked off, and scrolling through a 1000 person database to find the record is suboptimal. It'll be mostly filtering by last name, though I can see a use for any of the columns A-G to be filterable.

It does indeed remove the Autofilter from the Master sheet. I didn't know this!
Testing here shows that the line which applies the Advanced Filter removes the Autofilter from the MASTER sheet.
Any filters that were present in the results sheets are removed because a result sheet is cleared out before updating it.
I couldn't duplicate autofilters removed from sheets in the same workbook that were not concerned - these remained.


Yeah, I just want to reapply filters to all headers every time the code runs, is there a way to write this in post-refresh?
 
Last edited:
It's mostly for ease of record editing that I want the Master sheet to be filtered. There will be shift changes, there will be tasks that need to be checked off, and scrolling through a 1000 person database to find the record is suboptimal. It'll be mostly filtering by last name, though I can see a use for any of the columns A-G to be filterable.



Yeah, I just want to reapply filters to all headers every time the code runs, is there a way to write this in post-refresh?

Could we perhaps apply data filters on all tabs via macro the same way you were talking about doing the formatting? Applying the filters doesn't cause the page to refresh, so if we add a line after the existing code runs to apply filters on all headers for Sheet1-Sheet20, for instance, should be okay right? Then it will reapply every time the code runs, so should be good to go.

Another task I don't know how to do. I should really learn VBA.
 
l think I want to treat the MASTER sheet separately from the results sheets. I think can re-apply existing filters to MASTER sheet.
Regarding the results sheets, are you saying that you want to keep (re-apply) any existing filters that happen to be there on a sheet-by-sheet basis? If so, it's possible but will involve some coding. Also, if that's the case, what happens when you have a filter on the MASTER sheet which hides all of one type and the corresponding results sheet has filters in place to show only that one type? In summary, what happens when the filters on the MASTER sheet clash with a results sheet?
 
Last edited:
l think I want to treat the MASTER sheet separately from the results sheets. I think can re-apply existing filters to MASTER sheet.
Regarding the results sheets, are you saying that you want to keep (re-apply) any existing filters that happen to be there on a sheet-by-sheet basis? If so, it's possible but will involve some coding. Also, if that's the case, what happens when you have a filter on the MASTER sheet which hides all of one type and the corresponding results sheet has filters in place to show only that one type? In summary, what happens when the filters on the MASTER sheet clash with a results sheet?

Oh, no, I'm sorry. We're speaking at cross-purposes again.

What I want is the filter setting applied to the header ROW on all sheets. (That is, when you highlight Row 1, go to the Data tab in Excel and click on "Filter" it creates dropdowns on each header to allow the user to filter at-will.)

I want that. No SPECIFIC filter, just removing the step of applying filter options to the header so it's there, ready for the user. (This is, again, simplification for the end user.) May seem trivial, but every step I can remove from the end-user is time and money saved.
 
Last edited:
The following code
1. restores any autofilter in the MASTER sheet removed by advanced filtering, including what ws filtered for
2. adds conditional formatting to the results sheet (both black for blanks and alternate row colours
3. adds an autofilter to each results sheet
4. Hides/shows about 700 columns on each results sheet. This last takes time. You may want to put a button on the MASTER sheet to do refresh the results sheets so that it only happens when you click it?
Code
Code:
Sub blah()
Dim strAFilterRng As String    ' Autofilter range
Dim varFilterCache()           ' Autofilter cache
Dim wksAF As Worksheet
' [set up code]
Application.ScreenUpdating = False
Set wksAF = Worksheets("MASTER")
SaveFilters wksAF, strAFilterRng, varFilterCache
Set SourceList = wksAF.Range("A1:XZ1000")
For Each Sht In Sheets(Array("Area1g", "Area2k", "Area3w", "Area4a"))
  With Sht
    .UsedRange.Clear
    .Cells.FormatConditions.Delete
    On Error Resume Next: .Names("Extract").Delete: On Error GoTo 0
    .Range("AAA1:AAA2") = Application.Transpose(Array("Area", "*" & Sht.Name & "*"))
    SourceList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("AAA1:AAA2"), CopyToRange:=.Range("A1"), Unique:=False
'   Application.Goto .UsedRange.Offset(1)
    .Range("A1").AutoFilter
    .Cells.EntireColumn.AutoFit
    For Each colm In .UsedRange.Offset(1).Resize(, 703).Columns  ' limited to 170 columns, you can adjust.
      If Application.WorksheetFunction.CountBlank(colm) = colm.Rows.Count Then colm.EntireColumn.Hidden = True Else colm.EntireColumn.Hidden = False
    Next colm
    Set uuu = .Range("A1").CurrentRegion
    Set uuu = uuu.Offset(1).Resize(uuu.Rows.Count - 1)
    uuu.FormatConditions.Add(Type:=xlBlanksCondition).Interior.ColorIndex = 1
    With uuu.FormatConditions.Add(Type:=xlExpression, Formula1:="=ISEVEN(ROW())").Interior
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorLight2
      .TintAndShade = 0.599963377788629
    End With
  End With
Next Sht
' Restore original autofilter if present ..
RestoreFilters wksAF, strAFilterRng, varFilterCache
Application.ScreenUpdating = True
End Sub
' Usage example:
'    Dim strAFilterRng As String    ' Autofilter range
'    Dim varFilterCache()           ' Autofilter cache
'    ' [set up code]
'    Set wksAF = Worksheets("Configuration")
'
'    ' Check for autofilter, turn off if active..
'    SaveFilters wksAF, strAFilterRng, varFilterCache
'    [code with filter off]
'    [set up special auto-filter if required]
'    [code with filter on as applicable]
'    ' Restore original autofilter if present ..
'    RestoreFilters wksAF, strAFilterRng, varFilterCache

'~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Sub:      SaveFilters
' Purpose:  Save filter on worksheet
' Returns:  wks.AutoFilterMode when function entered
'
' Arguments:
'   [Name]      [Type]  [Description]
'   wks         I/P     Worksheet that filter may reside on
'   FilterRange O/P     Range on which filter is applied as string; "" if no filter
'   FilterCache O/P     Variant dynamic array in which to save filter
'
' Author:   Based on MS Excel AutoFilter Object help file
'
' Modifications:
' 2006/12/11 Phil Spencer: Adapted as general purpose routine
' 2007/03/23 PJS: Now turns off .AutoFilterMode
' 2013/03/13 PJS: Initial mods for XL14, which has more operators
'
' Comments:
'----------------------------
Function SaveFilters(wks As Worksheet, FilterRange As String, FilterCache()) As Boolean
Dim ii As Long

FilterRange = ""    ' Alternative signal for no autofilter active
SaveFilters = wks.AutoFilterMode
If SaveFilters Then
  With wks.AutoFilter
    FilterRange = .Range.Address
    With .Filters
      ReDim FilterCache(1 To .Count, 1 To 3)
      For ii = 1 To .Count
        With .Item(ii)
          If .On Then
            #If False Then  ' XL11 code
              FilterCache(ii, 1) = .Criteria1
              If .Operator Then
                FilterCache(ii, 2) = .Operator
                FilterCache(ii, 3) = .Criteria2
              End If
            #Else   ' first pass XL14
              Select Case .Operator
                Case 1, 2   'xlAnd, xlOr
                  FilterCache(ii, 1) = .Criteria1
                  FilterCache(ii, 2) = .Operator
                  FilterCache(ii, 3) = .Criteria2
                Case 0, 3 To 7  ' no operator, xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent, xlFilterValues
                  FilterCache(ii, 1) = .Criteria1
                  FilterCache(ii, 2) = .Operator
                Case Else    ' These are not correctly restored; there's someting in Criteria1 but can't save it.
                  FilterCache(ii, 2) = .Operator
              End Select
            #End If
          End If
        End With  ' .Item(ii)
      Next
    End With  ' .Filters
  End With  ' wks.AutoFilter
  wks.AutoFilterMode = False  ' turn off filter
End If  ' wks.AutoFilterMode
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Sub:      RestoreFilters
' Purpose:  Restore filter on worksheet
' Arguments:
'   [Name]      [Type]  [Description]
'   wks         I/P     Worksheet that filter resides on
'   FilterRange I/P     Range on which filter is applied
'   FilterCache I/P     Variant dynamic array containing saved filter
'
' Author:   Based on MS Excel AutoFilter Object help file
'
' Modifications:
' 2006/12/11 Phil Spencer: Adapted as general purpose routine
' 2013/03/13 PJS: Initial mods for XL14, which has more operators
'
' Comments:
'----------------------------
Sub RestoreFilters(wks As Worksheet, FilterRange As String, FilterCache())
Dim col As Long

wks.AutoFilterMode = False  ' turn off any existing auto-filter
If FilterRange <> "" Then
  wks.Range(FilterRange).AutoFilter  ' Turn on the autofilter
  For col = 1 To UBound(FilterCache(), 1)
    #If False Then  ' XL11
      If Not IsEmpty(FilterCache(col, 1)) Then
        If FilterCache(col, 2) Then
          wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1), Operator:=FilterCache(col, 2), Criteria2:=FilterCache(col, 3)
        Else
          wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1)
        End If
      End If
    #Else
      If Not IsEmpty(FilterCache(col, 2)) Then
        Select Case FilterCache(col, 2)
          Case 0  ' no operator
            wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1)  ' Do NOT reload 'Operator'
          Case 1, 2   'xlAnd, xlOr
            wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1), Operator:=FilterCache(col, 2), Criteria2:=FilterCache(col, 3)
          Case 3 To 6  ' xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent
            #If True Then
              wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1)  ' Do NOT reload 'Operator' , it doesn't work
' wks.AutoFilter.Filters.Item(col).Operator = FilterCache(col, 2)
            #Else  ' Trying to restore Operator as well as Criteria ..
' Including the 'Operator:=' arguement leads to error.
' Criteria1 is expressed as if for a FALSE .Operator
              wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1), Operator:=FilterCache(col, 2)
            #End If
          Case 7  'xlFilterValues
            wks.Range(FilterRange).AutoFilter field:=col, Criteria1:=FilterCache(col, 1), Operator:=FilterCache(col, 2)
            #If False Then  ' Switch on filters on cell formats
' These statements restore the filter, but cannot reset the pass Criteria, so the filter hides all data.
' Leave it off instead.
            Case Else   ' (Various filters on data format)
              wks.Range(FilterRange).AutoFilter field:=col, Operator:=FilterCache(col, 2)
            #End If  ' Switch on filters on cell formats
        End Select
      End If
    #End If     ' XL11 / XL14
  Next col
End If
End Sub
 
The following code
1. restores any autofilter in the MASTER sheet removed by advanced filtering, including what ws filtered for
2. adds conditional formatting to the results sheet (both black for blanks and alternate row colours
3. adds an autofilter to each results sheet
4. Hides/shows about 700 columns on each results sheet. This last takes time. You may want to put a button on the MASTER sheet to do refresh the results sheets so that it only happens when you click it?

This is exactly what I wanted.

Regarding the button: while I like the idea, I'm afraid my users would forget to hit the button and thereby break the worksheet =/ It's not taking too long with 1000 columns right now, I believe the current functionality will do.

Thank you so much!
 
Back
Top