Using VBA to populate cells based on a criteria in another tab

Hi, I really want to learn VBA and its many uses in excel, but I'm more of a hands on learner. I have a simple project I'm trying to do for work that I know is possible through VBA, but I don't have the expertise to do it.

Essentially we have a master list of property managers (PM) addresses and owners etc... in a tab called "Property List" and tabs for each of the property managers in the same file. I would like to populate the individual PM's tabs with their properties based on a search/copy/paste of the "Property List" tab

Row 1 contains the headers ... there are 10 columns of information
Row 2 begins the data
Column 1 contiains the property manager Initial (the search criteria)

I've tried this code but get a runtime error object required

Sub copyrows()
     
    Dim tfCol As Range, Cell As Object
     
    Set tfCol = Range("A:A") 'Substitute with the range which includes your True/False values
     
    For Each Cell In tfCol
         
        If IsEmpty(Cell) Then
            Exit Sub
        End If
         
        If Cell.Value = "BC" Then
            Cell.EntireRow.Copy
            Brad.Select 'Substitute with your sheet
            ActiveSheet.Range("A65536").End(xlUp).Select
            Selection.Offset(1, 0).Select
            ActiveSheet.Paste
        End If
         
    Next
     
End Sub

Any helpful hints or suggestions is greatly appreciated. I would really like to learn this stuff so if you have suggestions on tutorials etc... I'm open!

Sub copyrows()
    Dim Cell As Range
    LR = Sheets("Brad").Range("A65536").End(xlUp).Row + 1
    For Each Cell In Range("A:A")
        If IsEmpty(Cell) Then Exit Sub
        If Cell.Value = "BC" Then
            Cell.EntireRow.Copy Sheets("Brad").Cells(LR, 1)
            LR = LR + 1
        End If
    Next
End Sub

Originally Posted by cplettner

I need the code to be dynamic enough that everytime we remove and/or add a property manager I don't have to go in and change the code.

you are not asking for suggestions, but for a turnkey package.

Ok?? I think I understand what you are saying. I'm willing to do this on my own if you could point me to resources I could study. I really do want to learn this stuff, but don't know where to start?

Hi, cplettner,

work through the range of all managers, apply an Autofilter to the sheet List with each single name, copy all visible cells to a new sheet, name that sheet. Maybe have a look at this:

Sub EF941913()
Dim rngCell As Range
Dim rngArea As Range
Dim wsList As Worksheet
Dim wsManag As Worksheet
Dim wsNew As Worksheet
Dim lngLast As Long

Set wsList = Sheets("Property List")
Set wsManag = Sheets("Property Managers")

With wsManag
  Set rngArea = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

For Each rngCell In rngArea
  With wsList
    If .AutoFilterMode Then .Range("A1").AutoFilter
    .Range("A1").AutoFilter
    .Range("A1").AutoFilter , field:=1, Criteria1:=rngCell
    lngLast = .Range("A" & Rows.Count).End(xlUp).Row
    If lngLast > 1 Then
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))
      wsNew.Range("A1").PasteSpecial xlValues
      wsNew.Name = rngCell.Offset(0, 1).Value
      Set wsNew = Nothing
    End If
    .Range("A1").AutoFilter
  End With
Next rngCell

Set rngArea = Nothing
Set wsManag = Nothing
Set wsList = Nothing
Application.CutCopyMode = False

End Sub

Ciao,
Holger

I have no idea about your workbook

Hi, cplettner,

there are different ways to solve this: keep the sheets and clear the contents if teh code finds a suiting name. Or as you said delete all sheets apart from the two which are part of the sample workbook.

Itīs up to you to decide what you think is suiting your taste/situation.

Ciao,
Holger

Hi, cplettner,

code for keeping the sheets and only adding new ones if new names are introduced may look like this:

Sub EF941913_2()
Dim rngCell As Range
Dim rngArea As Range
Dim wsList As Worksheet
Dim wsManag As Worksheet
Dim wsNew As Worksheet
Dim lngLast As Long

Set wsList = Sheets("Property List")
Set wsManag = Sheets("Property Managers")

With wsManag
  Set rngArea = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

For Each rngCell In rngArea
  With wsList
    If .AutoFilterMode Then .Range("A1").AutoFilter
    .Range("A1").AutoFilter
    .Range("A1").AutoFilter , field:=1, Criteria1:=rngCell
    lngLast = .Range("A" & Rows.Count).End(xlUp).Row
    If lngLast > 1 Then
      If WorksheetExists(rngCell.Offset(0, 1).Value) = False Then
        Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))
      Else
        Set wsNew = Worksheets(rngCell.Offset(0, 1).Value)
        wsNew.UsedRange.ClearContents
      End If
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      wsNew.Range("A1").PasteSpecial xlValues
      wsNew.Name = rngCell.Offset(0, 1).Value
      Set wsNew = Nothing
    End If
    .Range("A1").AutoFilter
  End With
Next rngCell

Set rngArea = Nothing
Set wsManag = Nothing
Set wsList = Nothing
Application.CutCopyMode = False

End Sub

Function WorksheetExists(ByVal WorksheetName As String) As Boolean

    On Error Resume Next
    WorksheetExists = (Sheets(WorksheetName).Name <> "")
    On Error GoTo 0

End Function

Ciao,
Holger

Hi, cplettner,

two options from my point: either stay with what yoiu have right now an dthen loop thorugh all sheets and see if the name is in the list, or get your first idea out again to delete all sheets except those with data to distribute and then build the managers sheets up again.

Ciao,
Holger