Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: On Error Resume Next

  1. #1
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default On Error Resume Next

    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?

    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 by StuartHall; Oct 3rd, 2013 at 01:36 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,687
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    5 Thread(s)

    Default Re: On Error Resume Next

    A quick fix (add red lines):
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Next

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

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,532
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error Resume Next

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

    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


    HTH,

  5. #5
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Next

    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 ) ?

  6. #6
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,532
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error Resume Next

    You can test it like this:

    Debug.Print SheetExists("E-Video")

  7. #7
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Next

    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

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,687
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    5 Thread(s)

    Default Re: On Error Resume Next

    Quote Originally Posted by StuartHall View Post
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Next

    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.

  10. #10
    New Member
    Join Date
    Sep 2013
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error Resume Next

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •