Fill New Sheets With Data From Other Sheets

Question

I have a master sheet that has rows of house address data, one column of which is a "State" column. I have 50 other sheets in the same document, corresponding to each of the 50 USA states. What I want to happen: When I enter an address in the master sheet, and enter a state (say "CA" or "California"), I want it to automatically populate the "CA" sheet with that address. Thanks!

Answer 1

Several assumptions here:

1) The State field is the last to be filled in, so that once you enter the state, you can immediately copy the address to the correct worksheet.

2) You enter your states with the abbreviation and you have worksheets with names that exactly match state abbreviations.

3) The state worksheets' data is contiguous and begins in A1.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const lngSTATECOLUMN As Long = 6

    Dim wks As Worksheet
    Dim lngNextAvailableRow As Long

    ' check that only a single cell is being changed '
    If Target.Areas.Count = 1 And Target.Cells.Count = 1 Then

        ' check that the cell being edited is in the state column '
        If Not Intersect(Target, Columns(lngSTATECOLUMN)) Is Nothing Then

            ' check that a two-character entry has been made in the state column '
            If Len(Target.Value) = 2 Then

                ' turn off error checking in case it cannot find a matching worksheet '
                On Error Resume Next
                Set wks = ThisWorkbook.Worksheets(Target.Value)
                On Error GoTo 0

                ' continue if it found a worksheet with the same name as the state you input '
                If Not wks Is Nothing Then

                    lngNextAvailableRow = wks.Range("a1").CurrentRegion.Rows.Count + 1
                    ActiveSheet.Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).Copy _
                        wks.Range("A" & lngNextAvailableRow)

                End If
            End If
        End If
    End If

End Sub

I would, however, ask why you need to duplicate your data in 50 different worksheets? This seems hugely inefficient and prone to errors. Unless you need to do this for a very specific reason, I would very strongly advise against it.

If you need at some point to display or use separate state addresses, then I would look instead at filtering the main list of addresses.