Repeating a Loop With Changes

[Origamifan92]

I have some code that I'm trying to simplify, shown below. The overall macro is used to clear several sheets but one, sort all rows in the filled sheet, then copy and paste from one sheet to another according to values in a column. It works quickly and well, sorting about 70 lines in under 15 seconds, but is rather redundant. I've included an example showing how it's redundant. The code is basic enough to check for a value, then copy the line and paste in another sheet, but I'm wondering if there's a way to change it that, rather than have several lines and several IF statements, it's possible to condense the whole thing that it will alternate the desired checked value and sheets.

'Begin one sort loop

        If check_value = "Administrative" Then

            ActiveCell.EntireRow.Copy

            Sheets("Admin").Select

            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1

            Range("A" & Rows.Count).End(xlUp).Offset(1).Select

            ActiveSheet.Paste

            Application.ScreenUpdating = False

            Columns(6).EntireColumn.Delete

            Application.ScreenUpdating = False

            Sheets("Master").Select

            Application.ScreenUpdating = False

        End If

'End one sort loop

        If check_value = "Care Mgmnt" Then

            ActiveCell.EntireRow.Copy

            Sheets("Care Mgmnt").Select

            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1

            Range("A" & Rows.Count).End(xlUp).Offset(1).Select

            ActiveSheet.Paste

            Application.ScreenUpdating = False

            Columns(6).EntireColumn.Delete

            Application.ScreenUpdating = False

            Sheets("Master").Select

            Application.ScreenUpdating = False

        End If

 

[Yongle]

All sorting happens in the 10 lives below "Sort Master Sheet"
- those 10 lines are fine

The rest of the code is not doing any sorting
- it simply copying and pasting
- and that has already been largely dealt with above

So are you now ok, or are you still wanting some help?

 

[Origamifan92]

I apologize, I was a little unclear. I would like it to do both. I have the sorting there to have the Master sheet organized for quick reference, and then copy from the check value in column f to the appropriately named tab, but I wanted the code to be a little more elegant, per se, than a chunky repetitive copy-pasted If statement. As we started making the edits, I deleted the sort loops, and started redefining check values under where I said to start a sort loop.i apologize if that's clear as mud :P

 

[Yongle]

This is how to loop through the possible values for variable check_value

Please check that the sheet names that Select Case returns are correct
(the values have been extracted from your code so should be ok)

From what you say you are only sorting Master ONCE, in which case the code to sort Master goes OUTSIDE and BEFORE the loop
I think you only want to copy ONE row (is that correct ??)
What I cannot tell from your code is how each check_value is found inside Master
- are you simply MANUALLY selecting the one you want ??
(in which case Set cel = ActiveCell)
- or is the code supposed to search and find the correct row for each check_value ??
(in which case Set cel = Sheets("Master").Columns("F").Find(check_value) - or some variant thereof to find the correct one if check_value is repeateded in column F)

If you need further help please detail EXACTLY you expect the code to do (in words) step by step

Sub HowToLoop()

    Dim sheet_name As String, check_value, ws As Worksheet, cel As Range
    
'sort
       [COLOR=#ff0000]'insert your sorting code here[/COLOR]

'loop   
    For Each check_value In Array("Administrative", "Part Time EEs", "Senior Centers", "Transportation", "Care Mgmnt", "Contracts", "County", "CSP", "Fiscal", "MOW", "Protective", "Technical", "Active")
'get sheet names
        Select Case check_value
            Case "Administrative":  sheet_name = "Admin"
            Case "Part Time EEs":   sheet_name = "PT EEs"
            Case "Senior Centers":  sheet_name = "Sr. Centers"
            Case "Transportation":  sheet_name = "Transport."
            Case Else:              sheet_name = check_value
        End Select
        
'set the correct sheet
        Set ws = Sheets(sheet_name)
    
        set cel=?????
'copy and paste
       cel.EntireRow.Copy Destination:=ws.Range("A" & Rows.Count).End(xlUp).Offset(2)
'delete column(s)
        ws.Columns(6).EntireColumn.Delete
        If sheet_name = "Active" Then ws.Columns(5).EntireColumn.Delete
    Next check_value
End Sub