Macro to filter two criterion, copy, and delete | VBA

Question

I'm VERY new to the world of VBA. My goal is to create a macro that will Filter out text "FL" and "CA" in column H, delete the row that contains them from the original raw data, and copy them to new individual workbooks. I was able to do this with one state, but when I go to add another I run into issues. Here is the code I have for Moving FL to another workbook:

Sub PMAPMoveFL()

    'Rename sheet 1
    ActiveSheet.Name = "Sheet1"
'Add new sheet and return to sheet 1
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
'Filter out FL, copy and paste to sheet 2
    Selection.AutoFilter
    ActiveSheet.Range("A1:A5000").AutoFilter Field:=8, Criteria1:="FL", Operator:=xlAnd
    ActiveSheet.UsedRange.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
'Delete FL from sheet 1
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.Delete
'Move FL sheet to new workbook
    Sheets("Sheet2").Select
    Sheets("Sheet2").Move
    If Range("A1") = "" Then
        MsgBox "This customer did not submit Florida data,you may delete this empty workbook"
        End If


End Sub

It was tricky for me because the number of rows will never be absolute, but the column where the State is located is(Column H).

THANK YOU IN ADVANCE !!!

Answer 1

I'll try to clean up the code a little bit, and we'll work on giving you a dynamic range, as opposed to a fixed range in the process.

Dim LR as Long 'LR is Last Row

ActiveSheet.Name = "Sheet1"

With Sheets("Sheet1")
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet2"

    LR = .Cells(.Rows.Count,1).End(xlUp).Row

    .Rows(1).AutoFilter
    .Range("A1:A5000").AutoFilter Field:=8, Criteria1:="FL", Operator:=xlAnd
    .Range("A1:K" & LR).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
End With

With Sheets("Sheet2")
    .Rows(1).Delete
    .Move
    If .Range("A1") = "" Then
        MsgBox "This customer did not submit Florida data,you may delete this empty workbook"
    End If
End With

I got rid of a few redundancies with this post. I also took out the deletion of Sheet1 data; I was unsure if you wanted the whole sheet removed or just the visible cells that show Florida results. Note that I arbitrarily used the last column as K, since it incorporates H within the A:K range.

I would guess that you want to store FL results somewhere else (another workbook) and keep the existing data, but I don't want to be wrong.

I would recommend the following code, in lieu of the above changes, which will copy Sheet1 to Sheet2, then perform separate actions on either, where Sheet1 deletes Florida Options and Sheet2 deletes non-Florida Options:

Dim i, k, LR as Integer

ActiveSheet.Name = "Sheet1"

With Sheets("Sheet1")
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Sheet2"
    LR = .Cells(.Rows.Count,1).End(xlUp).Row
    .Range("A1:K: & LR).Copy Sheets("Sheet2").Range("A1")

    For i = 2 to LR
        If .Cells(i,"H").Value="FL" Then
            .Rows(i).Delete
        End If
    Next i
End With

With Sheets("Sheet2")
    For k = 2 to LR
        If .Cells(k,"H").Value="FL" Then
        Else    
            .Rows(k).Delete
        End If
    Next k
End With

LR stays the same between the two sheets since the data is the same.