VBA Nested For Each Loop Help

[melkent]

I am new to VBA and new to the forum. I have spent hours trying to find how to make this macro and have had no success and appreciate any assistance. I am trying to make a nested loop The first loop creates a new worksheet from a template (if it doesn't already exist) for each cell in column DW, renames the sheet and moves to a different workbook. This loop works correctly.

However before that loop moves the newly named active sheet to a different workbook, I need the second loop to occur.
That loop should look at each circ name in range (Column DR2:DR) of the raw data sheet named Trees
and if cell A8 value on the new sheet (template that was created and renamed) matches the cell value in Column DW of row with circ name it is looking at ,
and If the circ name doesn't already exist in in Column D20:D on the new sheet (template that was created and renamed)
then copy row 20 of template insert copied row in template insert circ name in new row column D and sumifs in new row column E

If the cell value of DW doesn't match the value in cell A8 I want it to do nothing and go to next name in range. If the cell value matches DW but a row already exists for that name, then do nothing and go to next name in range. Once it has completed this loop for each name in range, return to first loop.

Here is what I have so far:
Sub SheetsFromTemplate()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsMASTER As Worksheet, wsTEMP As Worksheet
Dim UtilName As Range, nm As Range
Dim mybook As Workbook
Set wb = Application.ActiveWorkbook
Set mybook = _
Workbooks.Open _
("C:\Users\person\Documents\Ifolder\templatefile.xlsx")
mybook.Sheets("WO Template").Copy After:=wb.Sheets(wb.Sheets.Count)
mybook.Sheets("Client Data").Copy After:=wb.Sheets(wb.Sheets.Count)
Dim c1 As Range
Dim c2 As Range
Dim Circ As Range, cnm As Range
Dim rngs As Range
Set c2 = Worksheets("Trees").Range("DW:DW")
Set rngs = Worksheets("Trees").Range("I:I")
Set c1 = Worksheets("Trees").Range("DR:DR")


With ThisWorkbook                                                 'keep focus in this workbook
    Set wsTEMP = Worksheets("WO Template")                        'template to be copied
    Set wsMASTER = Worksheets("Trees")                            'sheet with names
    Set UtilName = wsMASTER.Range("DW2:DW" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas  'range to find names to be checked
    Set Circ = wsMASTER.Range("DR2:DR" & Rows.Count).SpecialCells(xlConstants)
    Application.ScreenUpdating = False                              'speed up macro
   
    For Each nm In UtilName                                         'check one name at a time
        If Not Evaluate("ISREF('" & CStr(nm.Text) & "'!A1)") Then   'if sheet does not exist
            wsTEMP.Copy After:=Sheets(.Sheets.Count)                'create it from template
            ActiveSheet.Name = CStr(nm.Text)                        'rename it
      
        Set Uname = Worksheets("Trees").Range("DW2:DW" & Rows.Count)
        Set cn = ActiveSheet.Range("D20:D")
            [COLOR=rgb(0, 0, 0)] For Each cnm In Circ                                 'Check one name at a time[/COLOR]
[COLOR=rgb(250, 197, 28)]                [/COLOR][COLOR=rgb(0, 0, 0)] If Uname = ActiveSheet.Range("A8") And WorksheetFunction.CountIf(cn, CStr(cnm.Text) = 0) Then [/COLOR]'if  UtilName matches cell A8 on newly created sheet and circ does not exist in column D rows...Then
                    Rows("20:20").Copy                               'copy Row 20 on active worksheet
                    ActiveSheet.Rows(20).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove  'insert new copied row with formulas format and data
                    Application.CutCopyMode = False
                    ActiveSheet.Range("D20") = CStr(cnm.Text)                                   'insert circ name in cell
                    ActiveSheet.Range("E20") = WorksheetFunction.SumIfs(rngs, c1, CStr(cnm.Text), c2, CStr(nm.Text)) 'insert sum in cell
            'if Util name in row with cnm matches ActiveSheet name (Alternatively matches ActiveSheet.Range("A8") AND cnm exists in ActiveSheet.Range("D20:D")
            'then go to next cnm
            'if Util name in row with cnm does not match ActiveSheet name(Alternatively matches ActiveSheet.Range("A8")
            ' then go to next cnm
                End If
            Next cnm
        ActiveSheet.Move Before:=Workbooks("Client VM WO Database").Sheets(1) ' move to wo workbook
        End If
    Next nm
    Application.ScreenUpdating = True                           'update screen one time at the end
End With

MsgBox "All sheets created"
End Sub

I know that the If Uname= line doesnt work but every variation I do either mismatches or lists every cnm line from the raw data with the correct value in column E on template.


This is an example of my raw data trees sheet

Column I	Column DR	Column DW
12	XZ67789	ABC
35	EHI23	DEF
4	pq12	GHI
99	EHI22	DEF
14	RS23	GHI
14	CVY998	ABC

Here is a table of what the template rows need to be starting at row 20 on
And my WO template with Util name in cell A8

	Column A	Column B	Column C	Column D	Column E
row 20	on sheet already	on sheet already	on sheet already
Row 21(copied row 20 inserted here)	copied row data	copied row data	copied row data	unique cnm for util	sumifs value
Row 22 (copied row 20 inserted here)	copied row data	copied row data	copied row data	unique cnm for util	sumifs value

 

[melkent]

I figured out what would work for me on this

Sub SheetsFromTemplate()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsMASTER As Worksheet, wsTEMP As Worksheet
Dim UName As Range, nm As Range
Dim mybook As Workbook
Set wb = Application.ActiveWorkbook
Set mybook = _
Workbooks.Open _
("C:\Users\person\Documents\Ifolder\templatefile.xlsx")
mybook.Sheets("WO Template").Copy After:=wb.Sheets(wb.Sheets.Count)
mybook.Sheets("Client Data").Copy After:=wb.Sheets(wb.Sheets.Count)
Dim c1 As Range
Dim c2 As Range
Dim Circ As Range, cnm As Range
Dim rngs As Range
Set c2 = Worksheets("Trees").Range("DW:DW")
Set rngs = Worksheets("Trees").Range("I:I")
Set c1 = Worksheets("Trees").Range("DR:DR")

With ThisWorkbook                                                                                                   
    Set wsTemp = Worksheets("WO Template")                                                                          
    Set wsMaster = Worksheets("Trees")                                                                            
    Set UName = wsMaster.Range("DW2:DW" & Rows.Count).SpecialCells(xlConstants)                                   
    
Application.ScreenUpdating = False                                                                                  

    For Each nm In UName                                                                                         
        If Not Evaluate("ISREF('" & CStr(nm.Text) & "'!A1)") Then                                                    
            wsTemp.Copy After:=Sheets("Client Data")                                                               
            ActiveSheet.Name = CStr(nm.Text)                                                                         '
        End If
    Next nm
    
    Dim mySheetNames() As Variant
    mySheetNames = Array("CUST1", "CUST5", "CUST32")
    Application.DisplayAlerts = False
    wb.Sheets(mySheetNames).Delete
    Application.DisplayAlerts = True

 End With
    

With ThisWorkbook                                                                                                  
    Set wsMaster = Worksheets("Trees")                                                                               
    Set UName = wsMaster.Range("DW2:DW" & Rows.Count).SpecialCells(xlConstants)                                   
    Dim un As Range
    Dim i As Long
    Dim wscount As Long
    wscount = Sheets.Count

 Application.ScreenUpdating = False                                                                                  
 
 For i = 4 To wscount                                                                                               
    Sheets(i).Activate                                                                                               
    Set un = ActiveSheet.Range("A8")                                                                                
        For Each nm In UName                                                                                     
            If nm = un Then                                                                                          
                nm.Offset(0, -5).Copy ActiveSheet.Range("D20")                                                      
                ActiveSheet.Range("E20") = WorksheetFunction.SumIfs(rngs, c1, nm.Offset(0, -5), c2, CStr(nm.Text))  
                ActiveSheet.Rows("20:20").Copy                                                                       
                ActiveSheet.Rows(20).Insert Shift:=xlDown                                                           
                Application.CutCopyMode = False
            Else                                                                                                     
        End If
    Next nm
    ActiveSheet.Range("A20:G" & Rows.Count).RemoveDuplicates Columns:=4                                                  
 Next i                                                                                                               
End With
Application.ScreenUpdating = True                                                                                  
MsgBox "All sheets created and updated"                                                                     
End Sub