On Error Resume Next

StuartHall

New Member
Joined
Sep 30, 2013
Messages
35
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?

Rich (BB code):
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
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
A quick fix (add red lines):
Rich (BB code):
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
 
Upvote 0
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,
 
Upvote 0
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 ) ?
 
Upvote 0
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?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top