Autoscroll through drop-down without hard coding the name of each value

[yunesm]

I have reports that generated and sent to me each month. For now I need to separate the data that's in each report by the physical locations in column A. The list of sites is dynamic and changes from month to month.

I'm looking for a way to select each unique value in column A, activate the range and save that range as a unique file without hardcode the actual value. I've done something similar in the past but I can't think of a simple way to do it in this exact situation.

I have my initial skeleton/idea below. Any suggestions would be greatly appreciated

Sub AutoscrollThroughPractices()

'--------------------------------------------------------------------------------------------
'   Set definitions...
'--------------------------------------------------------------------------------------------
Static iPracticeCount As Integer     'Count of unique practices
Dim rStart As Range
Dim rActive As Range
Dim iMyCount As Integer
Dim rPractice As Range
    Set rStart = Range("A1")
    Set rActive = Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
    iMyCount = CountUniqueValues(rPractice)
    Set rPractice = Range(Range("A2"), Range("A2").End(xlDown))


'    On Error Resume Next
'    '----------------------------------------------------------------------------------------
'    '   Modify alerts
'    '----------------------------------------------------------------------------------------
'    With Application
'    .DisplayAlerts = False
'    .ScreenUpdating = False
        
        '------------------------------------------------------------------------------------
        '   Count of practices reached
        '   Reset the static variable and exit
        '------------------------------------------------------------------------------------
        If iMyCount = iPracticeCount Then
                iPracticeCount = False

                'reset object variables
                Set rStart = Nothing
                Set rActive = Nothing
                Set rPractice = Nothing
         Exit Sub
    
        '------------------------------------------------------------------------------------
        '   Increment static variable by one until iMyCount  = Count of Practices reached
        '   If static variable is < iMyCount then add 1 to the static variable each time
        '   the macro is called
        '------------------------------------------------------------------------------------
        Else
            iPracticeCount = iPracticeCount + 1 'Add 1 tp static variable
            
            '--------------------------------------------------------------------------------
            '   Use something to increment through practice list
            '--------------------------------------------------------------------------------
'            rActive.AutoFilter Field:=1, Criteria1:=iPracticeCount
'           set a practice variable here
'           Dim sPractice
'               spractice = ???
                
                '----------------------------------------------------------------------------
                '   Select filterered range and print to new file with name in practice
                '----------------------------------------------------------------------------
                rActive.Activate
'                Create new file
'                Name new file with sFileName = "1 - Done Not Done_" & sPractice
'                save to dir:
            
                '----------------------------------------------------------------------------
                '   Re-call Autoscroll immediately
                '----------------------------------------------------------------------------
                Application.OnTime Now(), "AutoscrollThroughPractices"

        '------------------------------------------------------------------------------------
        '   End if check of count vs pcp count
        '------------------------------------------------------------------------------------
        End If

    'reset object variables
    Set rStart = Nothing
    Set rActive = Nothing
    Set rPractice = Nothing

'    '----------------------------------------------------------------------------------------
'    '   Modify alerts
'    '----------------------------------------------------------------------------------------
'    .DisplayAlerts = True
'    .ScreenUpdating = True
'    End With

'--------------------------------------------------------------------------------------------
'   End
'--------------------------------------------------------------------------------------------
End Sub

[yunesm]

I took a drive and the solution came to me. I did modify the above code and it works perfectly only scrolling through the unique values.

process I took: Create a temporary list of unique values, named the list, used index fx and a static variable to increment through the list and gernate a unique file for each selection.

Turned out to be pretty easy!!