Compile error Invalid Next control variable error

[iP_123]

I am trying to figure out why I get a compile error "Invalid Next control variable reference" when I run the below vba I inherited. and usually MyCell in the vba is highlighted

Sub RunMacro()


    Dim MyCell As Range
    
    Dim wkCYCompPL As Worksheet
    Dim wkCYTrendedPL As Worksheet
    Dim wkPYTrendedPL As Worksheet
    Dim wkACT As Worksheet
    Dim wkPLAN As Worksheet
    Dim wkEST As Worksheet
    Dim wkFCST As Worksheet
    Dim wkRST16 As Worksheet
    Dim wkHC As Worksheet
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim DateString As String
    Dim FolderName As String
    Dim sh As Worksheet
    Dim Ws As Worksheet
    Dim flg As Boolean
   
   Set wkCYCompPL = Sheets("CY Comp P&L")
   Set wkCYTrendedPL = Sheets("CY Trended P&L")
   Set wkPYTrendedPL = Sheets("PY Trended P&L")
   Set wkACT = Sheets("Actual (FirstPS)")
   Set wkPLAN = Sheets("Plan (FirstPS)")
   Set wkEST = Sheets("Estimate (Forecast)")
   Set wkFCST = Sheets("Fcst (Forecast)")
   Set wkRST16 = Sheets("PY (EES RE16)")
      
Dim sts As Long
    
  application.ScreenUpdating = False
  
  'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook


    'Create new folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd hh-ss")
    FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
    MkDir FolderName
  
  
  For Each MyCell In Sheets("Control").Range("A12").CurrentRegion.Cells
  
     
        wkACT.Activate
        Range("C1").Value = MyCell.Value
        Range("C1").Select
        sts = EssMenuVRetrieve
        If sts <> 0 Then
            MsgBox "There was a problem retrieving data in the " & ActiveSheet.Name & "."
        End If




        wkPLAN.Activate
        Range("C1").Value = MyCell.Value
        Range("C1").Select
        'Retrieve code goes here
        sts = EssMenuVRetrieve
        If sts <> 0 Then
            MsgBox "There was a problem retrieving data in the " & ActiveSheet.Name & "."
        End If
        
        
        wkEST.Activate
        Range("C1").Value = MyCell.Value
        Range("C1").Select
        'Retrieve code goes here
        sts = EssMenuVRetrieve
        If sts <> 0 Then
            MsgBox "There was a problem retrieving data in the " & ActiveSheet.Name & "."
        End If
        
        
        wkFCST.Activate
        Range("C1").Value = MyCell.Value
        Range("C1").Select
        'Retrieve code goes here
        sts = EssMenuVRetrieve
        If sts <> 0 Then
            MsgBox "There was a problem retrieving data in the " & ActiveSheet.Name & "."
        End If
        
        
        wkRST16.Activate
        Range("C1").Value = MyCell.Value
        Range("C1").Select
        'Retrieve code goes here
        sts = EssMenuVRetrieve
        If sts <> 0 Then
            MsgBox "There was a problem retrieving data in the " & ActiveSheet.Name & "."
        End If
        
        
        application.DisplayAlerts = False
        
     ' Creates new worksheet
        'Worksheets.Add after:=Worksheets(Worksheets.Count)
        'Sourcewb.Sheets(9).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        
        Sheets(Array("CY Comp P&L", "CY Trended P&L", "PY Trended P&L")).Select
        'Sheets("CY Trended P&L").Activate
        Sheets(Array("CY Comp P&L", "CY Trended P&L", "PY Trended P&L")).Activate
        Sheets(Array("CY Comp P&L", "CY Trended P&L", "PY Trended P&L")).Copy
         
        
        'Sheets(Arrary(wkCYCompPL, wkCYTrendedPL, wkPYTrendedPL)).Copy After:=Worksheets(Worksheets.Count)
        
        'Sheets(Array("CY Comp P&L", "CY Trended P&L", "PY Tended P&L")).Copy After:=Worksheets(Worksheets.Count)
        
        'Workbooks("Sourcewb").Worksheets("WorkSheet").Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        'Sourcewb.Sheets(9).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        
        ActiveSheet.Name = Range("B3").Text
        
            
       ActiveWindow.Zoom = 90




    'ActiveWindow.DisplayGridlines = False
      
       
      'ActiveSheet.Select
      'Cells.Select
      'Selection.Copy
      'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


    ActiveWindow.DisplayGridlines = False
    
    
'Copy every sheet from the workbook with this macro
    Set Sourcewb = ThisWorkbook


    'Create new folder to save the new files in
    'DateString = Format(Now, "yyyy-mm-dd hh-ss")
    'FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
    'MkDir FolderName




   'Copy every visible sheet to a new workbook
    For Each sh In Sourcewb.Worksheets
    
    'For Each ws In Sheets
        'If sh.Visible = -1 Then
         '   sh.Select Not flg
          '  flg = True
        'End If
    'Next
    ActiveWindow.SelectedSheets.Copy
    
    
        'If the sheet is visible then copy it to a new workbook
        'If sh.Visible = -1 Then
            'sh.Copy


            'Set Destwb to the new workbook
            Set Destwb = ActiveWorkbook


            'Determine the Excel version and file extension/format
            With Destwb
                If Val(application.Version) < 12 Then
                    'You use Excel 97-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                Else
                    'You use Excel 2007-2013
                    If Sourcewb.Name = .Name Then
                        MsgBox "Your answer is NO in the security dialog"
                        'GoTo GoToNextSheet
                    Else
                    
                    FileExtStr = ".xlsm": FileFormatNum = 52
                    
                    
'                        Select Case Sourcewb.FileFormat
'                        Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
'                        Case 52:
'                            If .HasVBProject Then
'                                FileExtStr = ".xlsm": FileFormatNum = 52
'                            Else
'                                FileExtStr = ".xlsx": FileFormatNum = 51
'                            End If
'                        Case 56: FileExtStr = ".xls": FileFormatNum = 56
'                        Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
'                        End Select
                   End If
               End If
            End With
            
 'This Hides all WkSheets excepts for the relevant Sheet
For Each Ws In Worksheets
    If Ws.Name <> Sheets(11).Name Then
        Ws.Visible = xlSheetHidden
    End If
Next Ws




            'Save the new workbook and close it
            With Destwb
            DateString = Format(Now, "yyyy-mm-dd")
                .SaveAs FolderName _
                      & "\" & Destwb.Sheets(11).Name & " " & DateString & FileExtStr, _
                        FileFormat:=FileFormatNum
                .Close False
            End With
   
       'MsgBox "You can find the files in " & FolderName


    'With application
        '.ScreenUpdating = True
        '.EnableEvents = True
        '.Calculation = xlCalculationAutomatic
    'End With
    
   
   ActiveWindow.DisplayGridlines = True
          
  application.DisplayAlerts = False
  With ActiveWorkbook
  .Worksheets(.Worksheets.Count).Delete
       End With
  application.DisplayAlerts = True
          
     
    
    Next MyCell


    Sheets("Control").Select
    Sheets("Control").Range("C11:C11") = Now
    Range("A1").Select


    application.CutCopyMode = False
    application.ScreenUpdating = True
    
     
    'Add Report Completion for Input User
    MsgBox "Complete!!! You can find the files in " & FolderName


    With application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With


End Sub

I am not able to figure this out and I was hoping an assistance.

I appreciate your time

M

 

[Derek Brown]

You seem to have a "Next sh" missing.
Guessing that it should be located:

            Next sh

            'This Hides all WkSheets excepts for the relevant Sheet
            For Each Ws In Worksheets

 

[iP_123]

Thank you Sir. It looks like when I insert the "Next sh" it makes multiple copies of the Active Sheet which is not what I intend but it's my fault since I did not explain what I was trying to do.
What I am trying to do is to tweak the vba to make copies of the first 3 worksheets ("CYCompPL", "CYTrendedPL", and "PYTrendedPL") and rename all three based on the cell name in B3 and then export these 3 worksheets out of the workbook into a newly created folder by the vba.
But each time I run it the vba only formats (ie Activewindow.zoom and ActiveWindow.Display Gridlines = False) the last copied worksheet but not the other two and the folder is almost always empty after being created.

I will appreciate your help with this since I am clueless on what I am doing wrong.

Thanks