Copy To New Worksheets

[billandrew]

Hello All

Attempting to expand my VBA knowledge. I am trying to copy a specific value (This case Maine & Virginia) from Sheet 1 to the newly added Maine & Virginia worksheets. I am using the below code which was provided in part by this Forum.

I would also like to copy the header row & in the future add additional state named sheets and the data from Sheet 1 to those sheets.
Sub copyif()
Dim lr As Long, ws As Worksheets, lr2 As Long
Dim wsa As Worksheet

Application.ScreenUpdating = False
Set wsa = ActiveSheet
ThisWorkbook.Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "Maine"
ThisWorkbook.Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "Virginia"
wsa.Activate

lr = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = 1
For i = 2 To lr

If Cells(i, 3).Value = "Maine" Then
Worksheets("Maine").Rows(lr2).Value = Worksheets("Sheet1").Rows(i).Value

ElseIf Cells(i, 3).Value = "Virginia" Then
Worksheets("Virginia").Rows(lr2).Value = Worksheets("Sheet1").Rows(i).Value


lr2 = lr2 + 1
End If
Next i
Application.ScreenUpdating = True


End Sub

 

[Fluff]

Do you want to create a new sheet each time for every value in column C?

 

[billandrew]

Yes. and there may be values in column C which I would never want to create a new worksheet for. Example states on the West Coast.

 

[Fluff]

That makes things a bit more complex, especially as I have no idea which states are on the west coast!
Will there be many values you don't want a sheet for? if so what.
Also does your data start in A1?
and what is the final column?

 

[billandrew]

The data starts in A1 & there are 7 columns of data.

 

[My Aswer Is This]

You never said what states to Exclude.
I guess it would be Washington Oregon California and New Mexico. But my geography may be wrong.
We would need exact names as they are shown in column "A"

 

[billandrew]

Right now I would like to include approximately 7 states

New York
Virginia
Maine
Massachusetts
New Hampshire
Vermont
New Jersey

However there may be all the remaining states in the data.

Sir

you provided code earlier (below) This code works perfect. I used this code, added the required sheets. i.e. The sheets named for the above states, 7 in total. The issue is if there are no sheets for the states I do not need, i.e Washington the code does not execute.

I would like to use an if cell(i,3). value = "Each of the states in need" then copy to the appropriate worksheet.

Hope this provides a better explanation.

 

[Fluff]

This will ask if you want a new sheet, for each value.

Sub AddSht_FltrPaste()
' billandrew (book11)

    Dim Cl As Range
    Dim UsdRws As Long
    Dim OSht As Worksheet
    Dim Ans As String
    
Application.ScreenUpdating = False

    Set OSht = Sheets("[COLOR=#ff0000]Input[/COLOR]")
    UsdRws = OSht.Range("C" & Rows.Count).End(xlUp).Row
    OSht.Range("A1:G1").AutoFilter

    With CreateObject("scripting.dictionary")
        For Each Cl In Range("C2:C" & UsdRws)
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Nothing
                If MsgBox("Do you want a sheet for " & Cl.Value & "?", vbYesNo) = vbYes Then
                    OSht.Range("A1:G" & UsdRws).AutoFilter field:=3, Criteria1:=Cl.Value
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Cl.Value
                    OSht.Range("A1:G" & UsdRws).SpecialCells(xlCellTypeVisible).Copy _
                        Sheets(Cl.Value).Range("A1")
                End If
            End If
        Next Cl
    End With
    OSht.Range("A1:G1").AutoFilter


End Sub

Change the value in red to match your sheet name

 

[billandrew]

When I said I would like to expand my knowledge of VBA I didn't think of this... This is amazing!. However is their a simpler method to copy using the method I sent.

The If then method....

 

[Fluff]

There is, but how many rows of data do you normally have?