On Error Resume Next

[StuartHall]

I'm building a consolidation workbook that imports two worksheets from several different workbooks. One sheet is called "Summary", one is called "E-video." Every workbook has a "Summary" sheet, but not every workbook has an "E-video".

My problem is that when I use On Error Resume Next (in order to skip past the workbooks that do not contain an E-Video sheet), VBA is treating all subsequent workbooks the same, only importing Summary sheets and not importing ANY E-Video sheets.

Does anyone have a solution?

Sub ManualStudiesConsolidation()
  Dim Filter As String, Title As String, msg As String
    Dim i As Integer, FilterIndex As Integer
    Dim Filename As Variant
       
    ' File filters
    Filter = "Excel Files (*.xlsm),*.xlsm," & _
            "All Files (*.*),*.*"
    '   Default filter to *.*
        FilterIndex = 3
    ' Set Dialog Caption
    Title = "Select File(s) to Open"
    ' Select Start Drive & Path
    ChDrive ("J")
    ChDir Sheets("Main").Range("f7").Value
    
        With Application
        ' Set File Name Array to selected Files (allow multiple)
        Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
        ' Reset Start Drive/Path
        ChDrive Left(.DefaultFilePath, 1)
        ChDir (.DefaultFilePath)
    End With
    ' Exit on Cancel
    If Not IsArray(Filename) Then
        MsgBox "No file was selected"
        Worksheets(Worksheets("Main").Range("e7").Value & " - Summary").Name = "Sheet2"
        Worksheets(Worksheets("Main").Range("e7").Value & " - Video").Name = "Sheet3"
        Exit Sub
    End If
    For i = LBound(Filename) To UBound(Filename)
    
    Application.DisplayAlerts = False
    Workbooks.OpenText Filename:= _
    Filename(i) _
       
     On Error Resume Next
       
            Sheets("Summary").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Summary").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("Summary").Name = "Summary" & Format(i, "0")
        
            Sheets("E-Video").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Sheets("E-Video").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("E-Video").Name = "E-Video" & Format(i, "0")
        
    
    Next i
    Filename(i).Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    
    Worksheets("Sheet2").Name = Worksheets("Main").Range("e7").Value & " - Summary"
    Worksheets("Sheet3").Name = Worksheets("Main").Range("e7").Value & " - Video"
 End Sub

 

[JoeMo]

A quick fix (add red lines):

For i = LBound(Filename) To UBound(Filename)
    
    Application.DisplayAlerts = False
    Workbooks.OpenText Filename:= _
    Filename(i) _
       
       On Error Resume Next
       
            Sheets("Summary").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Summary").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("Summary").Name = "Summary" & Format(i, "0")
        On Error GoTo 0
        On Error Resume Next
            Sheets("E-Video").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Sheets("E-Video").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("E-Video").Name = "E-Video" & Format(i, "0")
        On Error GoTo 0

 

[StuartHall]

Thanks for the prompt - but I'm still only getting "Summary" sheets.

 

[Smitty]

Instead of On Error, why not test for the existence of the E-Video sheet? Here's a function that will do it:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(SheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#007F00">' Returns TRUE if a sheet exists in the active workbook</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> x = ActiveWorkbook.Sheets(SheetName)<br>                <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> SheetExists = True _<br>                <SPAN style="color:#00007F">Else</SPAN> SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

HTH,

 

[StuartHall]

Hey thanks Smitty, but I'm not sure how I could use that. I'm getting an error on the: SheetExists(SheetName As String) As Boolean

Missing ) ?

 

[Smitty]

You can test it like this:

Debug.Print SheetExists("E-Video")

 

[StuartHall]

Sorry man but it's Amateur Hour over here. So am I nesting your original post like this? Or exactly how can I use your suggestion?

Sub ManualStudiesConsolidation()
  Dim Filter As String, Title As String, msg As String
    Dim i As Integer, FilterIndex As Integer
    Dim Filename As Variant
       
    ' File filters
    Filter = "Excel Files (*.xlsm),*.xlsm," & _
            "All Files (*.*),*.*"
    '   Default filter to *.*
        FilterIndex = 3
    ' Set Dialog Caption
    Title = "Select File(s) to Open"
    ' Select Start Drive & Path
    ChDrive ("J")
    ChDir Sheets("Main").Range("f7").Value
    
        With Application
        ' Set File Name Array to selected Files (allow multiple)
        Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
        ' Reset Start Drive/Path
        ChDrive Left(.DefaultFilePath, 1)
        ChDir (.DefaultFilePath)
    End With
    ' Exit on Cancel
    If Not IsArray(Filename) Then
        MsgBox "No file was selected"
        Worksheets(Worksheets("Main").Range("e7").Value & " - Summary").Name = "Sheet2"
        Worksheets(Worksheets("Main").Range("e7").Value & " - Video").Name = "Sheet3"
        Exit Sub
    End If
    For i = LBound(Filename) To UBound(Filename)
    
    Application.DisplayAlerts = False
    Workbooks.OpenText Filename:= _
    Filename(i) _
            
            Sheets("Summary").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Summary").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("Summary").Name = "Summary" & Format(i, "0")
    
Private Function SheetExists(SheetName As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(SheetName)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function
            Sheets("E-Video").Select
            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        Sheets("E-Video").Copy After:=Workbooks("MACRO!.xlsm"). _
        Sheets("Sheet3")
        Sheets("E-Video").Name = "E-Video" & Format(i, "0")
        
    Workbooks(2).Close SaveChanges:=False
    
    Next i
    
    Application.DisplayAlerts = True
    
    
    Worksheets("Sheet2").Name = Worksheets("Main").Range("e7").Value & " - Summary"
    Worksheets("Sheet3").Name = Worksheets("Main").Range("e7").Value & " - Video"
 End Sub

 

[JoeMo]

Sorry man but it's Amateur Hour over here. So am I nesting your original post like this? Or exactly how can I use your suggestion?

Just insert the red lines into the existing code where shown.

 

[StuartHall]

Trust me man, I'd use it - but it's still skipping. I need to code VBA to somehow rerun the entire Filename (i) from start to finish and only skip "E-video" if it isn't there on a workbook by workbook basis.

 

[StuartHall]

I appreciate both of your guys' help too, sincerely.