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



23098

[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