Conditional Formatting and advanced Filtering

[EliseFreedman]

Hi There

I have a large spreadsheet containing training data. I am trying to use an advanced filter to filter the data by manager so that I end up with a separate workbook for each manager which I then email to them.

I am using advanced filter to select the data from the main report "Colummns B:R(MyList Named Range) and create a filtered report - Starting in Column W on the same spreadsheet("Extract" Named Range"). I then copy the filtered report to a new workbook. This is all working fine.

Sub breakMyList()
    ' This macro takes values in the range myList
    ' and breaks it in to multiple lists
    ' and saves them to separate files.
    
    Dim Cell As Range
    Dim curPath As String
        Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim SheetNames As Variant
Dim i As Long
Dim LR As Long
    
    curPath = "h:\overduereports\"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each Cell In Range("lstSalesman")
        [valSalesman] = Cell.Value
        Range("myList").AdvancedFilter Action:=xlFilterCopy, _
            criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
        Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
        Workbooks.Add
        ActiveSheet.Paste
 
    
  
    
        ActiveWorkbook.SaveAs Filename:=curPath & Cell.Value & ".xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
        Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents

My issue is that I would like to be able to use conditional formatting to highlight where someones training is out of date. Initially when i first created the code, I was using vba to extract the records i wanted for each manager to a new workbook then conditionally formatting the workbook, applying borders etc. This all worked ok in testing but in reality with 800+ managers it is taking a huge amount of time to run. It worked fairly quickly when i removed all the formatting code. I then tried to apply conditional formatting to the named range thinking that i could do this once and it would remain in place each time the data was cleared (using clearcontents) ready for the next manager. However. the conditional formatting was removed when the data was cleared.

Is there any way that I can keep the conditional formatting in place whilst clearing the data from the range ready for the next manager in the list and preserve the conditional formatting when the new workbook is created

 

[SkipVought]

Elsie,

Yes, by making the target range a Structured Table with formatting. Or use a query rather than the Advanced Filter. The formatting might "stick" better using a query.

Alternatively, each of the managers could have access to ONE workbook where their network user id decodes to their manager name, so that each one can only see their own data. I'd put a timed Close on this workbook. Again the master data in a separate workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[EliseFreedman]

Thanks Skip

How would I give each of the managers access to ONE workbook where their network user id decodes to their manager name, so that each one can only see their own data. I'd put a timed Close on this workbook. Again the master data in a separate workbook.

 

[SkipVought]

In the Workbook_Open event.

Elsie, I'm going to need some help. I'm using me iPad remotely. I've been "out of the loop" now fie 2 years and enjoying retirement, but I've forgotten. It's something like...

MyUID = Environ("username")

Use this value to get the correct manager name in your table for the SQL.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Hey Elsie, any progress/solution?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[EliseFreedman]

I managed to get it to recognise the persons login. However, I didn't bargain on the fact that some people have wierd logins(I had just assumed that their login was the first half of their email addresses). Also didn't think to factor in cases where managers PA's were going in on their behalf to get the info. Ended up changing it to just ask for their full name (which they type in) and then filtering the data based on that

 

[SkipVought]

Great!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}