Next without for Error

[Likith]

Hello ,

I am trying to merge few sheets from other works into Master Workbook using following code but Its showing " Compile Error : Next without for "

Do you have any idea about this and suggest some Ideas

Sub MergeSheets2()
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
 
xStrPath = "C:\Users\reiu079\Desktop\09-07-19\Work Plans -FY20"
xStrName = "Performance,Development,Profile"
 
xArr = Split(xStrName, ",")
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
Next xI
End If
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Thanks for your help in advance !!

 

[Fluff]

If you indent your code it's easier to find problems like this.
You have the End If in the wrong place

Sub MergeSheets2()
   Dim xStrPath As String
   Dim xStrFName As String
   Dim xWS As Worksheet
   Dim xMWS As Worksheet
   Dim xTWB As Workbook
   Dim xStrAWBName As String
   Dim xI As Integer
   On Error Resume Next
    
   xStrPath = "C:\Users\reiu079\Desktop\09-07-19\Work Plans -FY20"
   xStrName = "Performance,Development,Profile"
    
   xArr = Split(xStrName, ",")
    
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   Set xTWB = ThisWorkbook
   xStrFName = Dir(xStrPath & "*.xlsx")
   Do While Len(xStrFName) > 0
      Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
      xStrAWBName = ActiveWorkbook.Name
      For Each xWS In ActiveWorkbook.Sheets
         For xI = 0 To UBound(xArr)
            If xWS.Name = xArr(xI) Then
               xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
               Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
               xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
               Exit For
      [COLOR=#ff0000]      End If
         Next xI
      Next xWS[/COLOR]
      Workbooks(xStrAWBName).Close
      xStrFName = Dir()
   Loop
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
End Sub

 

[Joe4]

When you nest things, you must close out the most recent thing first. Note this section:

    For Each xWS In ActiveWorkbook.Sheets
        For xI = 0 To UBound(xArr)
            If xWS.Name = xArr(xI) Then
                xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
                Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
                xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
                Exit For
[COLOR=#ff0000]             Next xI
        End If[/COLOR]
    Next xWS

So, the "Next xI" and "End If" lines need to be reversed.