Sorting Copying and Pasting By State Abbreviation

[stinkingcedar]

Hey guys, so here is my code:

Public Sub newcopy()


Dim WSCount As Long, StartCellRow As Long, i As Long
Dim sht As Worksheet
Dim region As String






WSCount = Worksheets.Count - 2
Application.ScreenUpdating = False


For i = 3 To WSCount + 2
    
    region = Sheets(i).Range("E1").Text
    Set sht = Sheets(i)
    Sheets(i).UsedRange
    StartCellRow = sht.Cells.Find("Please DO NOT TOUCH formula driven:", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
    sht.Range(sht.Cells(StartCellRow, 6), sht.Cells(StartCellRow + 29, 27)).Copy
    
    
Select Case region


    Case Is = "A"
    Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)


    Case Else
    Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)


End Select




Next i


End Sub

I am attempting to cycle through a workbook, and copy and paste a range to two different sheets based on a parameter. That parameter is that it will go to the first sheet if it contains a certain string in the text in cell E5, and it will go to the second if it does not.

My issue is that the string that is to be looked for in E5 can be 1 of 50 strings (the state abbreviations) so I am not exactly sure how to go about this. Any help with this would be greatly appreciated thank you!

 

[svendiamond]

Make a list of the state abbreviations in Z1:Z50 and then use the Match function to see if E5 matches any of the cells in Z1:Z50