VBA Nested For Each Loop Help

melkent

New Member
Joined
Apr 20, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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 IColumn DRColumn DW
12XZ67789ABC
35EHI23DEF
4pq12GHI
99EHI22DEF
14RS23GHI
14CVY998ABC

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 20on sheet alreadyon sheet alreadyon sheet already
Row 21(copied row 20 inserted here)copied row datacopied row datacopied row dataunique cnm for utilsumifs value
Row 22 (copied row 20 inserted here)copied row datacopied row datacopied row dataunique cnm for utilsumifs value
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

melkent

New Member
Joined
Apr 20, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I figured out what would work for me on this

VBA Code:
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
 
Solution

Forum statistics

Threads
1,176,042
Messages
5,901,092
Members
434,870
Latest member
michud08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top