new sheets created with a template and populated with data when conditions are met

[rskay14]

I have a workbook with three sheets: Collections, Client and Interest
New sheets created from a range in Sheets("Interest"). The range in Sheets("Interest") holds the client names.
Then the template in Sheets("Client") is copied and pasted into the new sheets

For each new sheets cell B6 holds the client name and .Range("A10:A1317") holds the dates
Sheets("Collections").Range("D10:D1317") holds the dates and .Range("D8:WC8") holds the Client Names
if Sheets("Collections").Range("D8:WC8") = new sheet.cells(6, 4) then
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if
move down on row and repeat the steps

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim MyClients As Range, MyColClients As Range, MyIntClients As Range
Dim clientNAME As String

With Sheets("Interest")
  Set MyIntClients = .Range("A5:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With Sheets("Collections")
    Set MyColClients = .Range("D8").Offset(0, 3).End(xlToLeft).Column
    Set colDATE = .Range("A10:A1317")
End With

For Each MyCell In MyIntClients
    If Trim(MyCell.Value) <> "" Then    'checks if cell is not empty
        If Not SheetExists(MyCell.Value) Then 'check if worksheet already exists
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
            'Copy worksheet("Client") to new worksheets
            Sheets("Client").Cells.Copy
            'paste to new worksheet
            Sheets(Sheets.Count).Paste
            'Fill cell B6 of new worksheets with client name
            ActiveSheet.Range("B6") = Sheets(Sheets.Count).Name
            
            'script to populate cells starts here
            'get the value of cell B6 into the string named clientNAME
            clientNAME = ActiveSheet.Range("B6").Value
            
            If clientNAME = MyColClients.Value Then 'Counter moves right until end of row (last column)
                If colDATE = ActiveSheet.Range("B10:B1317").Value Then 'checks if date on collection sheet same as client worksheet
                         Sheets("Collections").Range("D10").Offset(0, 3).End(xlToLeft).Column.Copy
                         ActiveSheet.Range("C10").Paste
                End If
                Sheets("Collections").Range("D10").Offset(1, 0).Select 'move one row down and repeat the condition above
            End If
            
        End If
    End If
Next MyCell

End Sub

Function SheetExists(shName As String) As Boolean
Dim sh As Object
SheetExists = False
For Each sh In Sheets
    If sh.Name = shName Then
        SheetExists = True
        Exit For
    End If
Next sh
End Function

[SamT]

Option Explicit

Sub CreateSheetsFromAList()
Dim Cel As Range, Rng As Range
Dim Clients As Range, CollectionClients As Range, InterestClients As Range
Dim ClientName As String

Dim ClientShtTemplate As Worksheet
Dim NewClientSht As Worksheet   'Demonstrate three ways
Dim InterestSht As Worksheet 'Second way
Dim CollectionsSht           'Third way

Dim CollectionDates As Range
Dim ClientsCollectionDates As Range

Set CollectionsSht = Worksheets("Collections")
Set InterestSht = Worksheets("Interest")
Set ClientShtTemplate = Worksheets("Clients")

''''Set Ranges
  With InterestSht
    Set InterestClients = Range(.Range("A5"), .Cells(Rows.Count, "A").End(xlUp))
  End With
  
  With CollectionsSht
    Set CollectionClients = Range(.Range("D10"), .Cells(10, Columns.Count).End(xlToLeft))
    Set CollectionDates = Range(.Range("A10"), .Cells(.Rows.Count, "A").End(xlUp))
  End With

''''Create Sheets for all Clients
'''''''''''''''''''''''''''''

''''Create new Client Sheet
  For Each Cel In InterestClients
    If Trim(Cel.Value) <> "" Then
      If Not SheetExists(Cel.Value) Then
        Sheets.Add After:=Sheets(Sheets.Count)
        Set NewClientSht = Sheets(Sheets.Count)
        
''''Fill new Client Sheet
        With NewClientSht
          .Name = Cel.Value
           ClientShtTemplate.Cells.Copy
          .Paste
          .Range("B6") = .Name
           ClientName = .Name
           Set ClientsCollectionDates = Range(.Range("B10"), Cells(Rows.Count, "B").End(xlUp))
        End With
        
        
''''I am lost at this point. I have commented it to show what it tells me.
        If ClientName = CollectionClients.Value Then 'Counter moves right until end of row (last column)
        'If String Variable = Large Range Then       'Say What? What counter?
          
          If CollectionDates = ClientsCollectionDates Then 'checks if date on collection sheet same as client worksheet
          'This is possible if every cell in one range = every cell in the other
            
            CollectionsSht.Range("D10").Offset(0, 3).End(xlToLeft).Column.Copy
            'Almost says copy the the column of first used Cell in Row 10. (the left of) Range("G10")
            
            NewClientSht.Range("C10").Paste
            'Paste that entire copied Column below ("C10")
          End If
            
            CollectionsSht.Range("D10").Offset(1, 0).Select 'move one row down and repeat the condition above
            'on every loop, select Range("D11"). Why?
        End If
            
        End If
    End If
Next Cel

End Sub

Edit to Add: now I come back and see that the ranges in your code do not match the ranges in your text.

[rskay14]

after the new client sheet is filled by setting value of cell B6 in new sheet same as Sheet tab name, i need to populate the other cells in the new client sheet as well.

Sheets("Collections").Range("D10:D1317") holds the dates
Sheets("Collections").Range("D8:WC8") holds the Client Names

if Sheets("Collections").Range("D8:WC8") = new sheet.Range(B6) then (Client name matches)
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then (Date Matches)
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if

do this until all data on Collection Sheet is posted in each client worksheet. Also when new data is input it should append if new and update if modifying.

[SamT]

That is an xlsx book. There is no code in an xlsx book.

[rskay14]

excel file reloaded as xls book

[rskay14]

Excel with code

[SamT]

I added colors to the workbook. In Sheet Collections the Source Cells are different colors. In sheets test1 and test2, the destination cells match the colors in Collections.

Is this what you want?

[rskay14]

yes that is what i want

[rskay14]

I need help for the macro to automatically fill in the fields when i have more than 20 customers.