Seeking help for creating a nested loop with condition

[Mysore]

Hello Friends

I have a perfectly working macro which will do the following.
1) Looping through a range of cells and based on the cell value, the report refreshes,
2) Creates the copy of this refreshed report and saves as a new worksheet within the same workbook and renames the worksheet
3) After the loop is completed, the workbook will hide all other worksheets except the new sheets which has created so that it can be exported to PDF or PPT format.

My new requirement.

The range of cells which is referenced before now has a condition being "YES" OR "NO". The loop needs to run only if each row in the range has as associated condition being "yes".
I do know how to add an If Statement following the FOR loop statement,however what I'm unable to crack is the way to fetch the Counter(Loop number) to join with Sheet name.
This is essential as I need to delete these temporary sheets in the workbook.


Any help to refine the code is much appreciated.

Sub NewSiteView()

Dim wb As Workbook
Dim DDLCount As Long
Dim TotalDDL As Long
Dim CurrentStr As String
Dim StartTime As Double
Dim MinutesElapsed As String


StartTime = Timer
Set wb = ActiveWorkbook


Application.ScreenUpdating = False




For Each ws In ActiveWorkbook.Worksheets


ws.Visible = xlSheetVisible


Next ws






For Each ws In Worksheets
    If ws.Name <> "Site View" And ws.Name <> "Lookups" Then ws.Visible = False
   
    Next ws
    
   
    
    Sheets("Site View").Activate
TotalDDL = Sheets("Lookups").Range("CountSite") + 1


 'Loops through Site List


 
    For DDLCount = 1 To TotalDDL
    Sheets("Site View").Activate
        Sheets("Site View").Range("Counter").Value = DDLCount
    CurrentStr = "Site View" & DDLCount




wb.Sheets("Site View").Copy After:=wb.Sheets(Sheets.Count)
wb.ActiveSheet.Name = CurrentStr
  
    
   
Next DDLCount
 


'Because only visable worksheets will be captured on PDF dump, need to hide temporarily
  
    Sheets(Array("Site View", "Lookups")).Visible = False




'Select all visible worksheets and export to PDF


 If Sheets("Site View").Range("FormatSelected").Value = "PDF" Then
    
    Call CreatePDF
 
 Else
 
    Call ExporttoPPT
    
    End If
    
   


'Unhide our original worksheet
    Dim wsname As Variant
    wb.Activate
    'getbook = ActiveWorkbook.Name
    'invisible = Array("Site View", "Lookups")
    'Sheets(invisible).Visible = False
    For Each wsname In Array(Sheet1.Name, Sheet6.Name, Sheet8.Name)
     Worksheets(wsname).Visible = True
         Next
    
    TotalDDL = Sheets("Lookups").Range("CountSite") + 1
 
'Delete all temp worksheets
    For DDLCount = 1 To TotalDDL
        CurrentStr = "Site View" & DDLCount
        Application.DisplayAlerts = False
        Sheets(CurrentStr).Delete
        Application.DisplayAlerts = True
    Next DDLCount
    


    
    DDLCount = Empty


'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")


'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
Application.ScreenUpdating = True






End Sub

VBA Code Image.jpg

[Leith Ross]

Hello Mysore,

Which cell in the row has the "Yes" or "No"?

Which range are these rows in?

[Mysore]

Hi Leith

Basically I have a sheet name "Lookups" which has a list of Sites with a named range called "NBOSite" referencing Column (J4:J59") . In column (M4:M59") I have either "Yes" or "No "against each site.

Thanks