Error handle routine for macro

[erock24]

How can I edit the following code. If the "Dload" tab is not there the code errors out. How can I make it so that if the "Dload" is not there the macro says "Dload tab missing. Do you want to check file and try again" If the user says yes. then the macro lets the user pick another file. If the user chooses No then does similar quesiton like I have here starting with

OpenA.Activate.

ChDrive "E:\"
    ChDir "E:\2007 Work\Analytical Review\Balance Sheet"

    TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Balance Sheet Review File")
    If TheFile2 = "False" Then
    OpenA.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
    Workbooks.Open Filename:=TheFile2
    Set OpenB = Workbooks(Workbooks.Count)
    
    OpenA.Activate
    Cells.Select
    Selection.Copy
    OpenB.Activate
    Sheets("Dload").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Thanks for your time and help.

 

[QuietRiot]

Sub Macro1()

Dim wsname As String

Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
       wsname = wsSheet.Name
       If wsname = "Dload" Then
       do something
       Else
       do something
       End If
       
    Next wsSheet


End Sub

 

[QuietRiot]

oh, i didn't read the 2nd half of your post.

let me tweak it for what you need

 

[erock24]

let me tweak it for what you need

Thank you for your time and help. This will really help me out.

 

[QuietRiot]

edited below

kind of hit a brain fart

try this, if i can come up with a better idea i'll post it.

i have no way of testing this so its untested

 

[erock24]

Well, Its getting there It seemed to work except.... problem is that I have a bunch of other stuff the macro does after it does the copy paste to the "dload" tab. Basically, it does this same thing but 14 more times. This code cuts all that out once it finds the right sheet. Also, if tab isn't found, and user decides to not try again I would like a similar error msg used like my other one, where it closes OpenA. then asks to go to IS Download, then if no, exit sub.

If TheFile2 = "False" Then 
    OpenA.Activate 
    ActiveWindow.Close 
      continue = MsgBox("Do you want to Continue to IS Download?", _ 
                    vbInformation + vbYesNo, _ 
                    "Continue?") 

If continue = 6 Then GoTo IncSt 
If continue = 7 Then Exit Sub

Seems like a lot of work and a mess, but if anyone can get me the 1st one I think I can do the others.
Thanks for your time and help.

 

[QuietRiot]

can you just put all the other stuff it does

right before the exit sub in this part:

If wsname = "Dload" Then 
        
    Set OpenB = Workbooks(Workbooks.Count) 
    
    OpenA.Activate 
    Cells.Select 
    Selection.Copy 
    OpenB.Activate 
    Sheets("Dload").Select 
    Cells.Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
' rest of code    
Exit Sub

 

[erock24]

Well, I don't know becaue I want to use this error handler again on the next one. Here is what the code looks like from begging to the 2nd one. Note: the 2nd one is special because it opens 4 sheets not just 2.

Sub Monthly()
Dim BS As Long, Dot As Long
Dim Oldfilename As String
Dim Oldfilepath As String
Dim Newfilename As String
Dim OpenA As Workbook, OpenB As Workbook
Dim TheFile As String
Dim TheFile2 As String
Dim myReturn&
Dim continue&
Application.ScreenUpdating = False

myReturn = MsgBox("Do you want to do Monthly Balance Sheet?", _
                    vbInformation + vbYesNo, _
                    "Monthly Balance Sheet?")

If myReturn = 6 Then GoTo Monthly
Range("B10,G10").Value = "N/A"
Application.ScreenUpdating = True
Application.ScreenUpdating = False
If myReturn = 7 Then GoTo SkipMonthly

Monthly:
   NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
   Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")
If TheFile = "False" Then
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
      Oldfilename = TheFile

    ' Separate the file path and file name
      BS = InStrRev(Oldfilename, "\")
      Oldfilepath = Left(Oldfilename, BS)
      Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
    
    ' Add the CSV extension to the file name
      Dot = InStr(1, Newfilename, ".")
      Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
    
    ' Rename the file
      Name Oldfilename As Newfilename

    Workbooks.Open Filename:=Newfilename
     
    Set OpenA = Workbooks(Workbooks.Count)
    
    ChDrive "E:\"
    ChDir "E:\2007 Work\Analytical Review\Balance Sheet"

    TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Balance Sheet Review File")
    If TheFile2 = "False" Then
    OpenA.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
    Workbooks.Open Filename:=TheFile2
    Set OpenB = Workbooks(Workbooks.Count)
    
    OpenA.Activate
    Cells.Select
    Selection.Copy
    OpenB.Activate
    Sheets("Dload").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    OpenA.Activate
    ActiveWindow.Close
    Workbooks("Analytical Review Guide").Sheets(1).Range("vbs").Value _
= IIf((Application.CountIf(OpenB.Sheets("Validation").Range("D3:E3"), "OK") = 2), "OK", "Error")
    OpenB.Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    
    Range("mbs").FormulaR1C1 = "X"
    Application.ScreenUpdating = True
    
SkipMonthly:
IncSt:
Application.ScreenUpdating = False
    
    NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
    Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File FRPMISSUM")
    If TheFile = "False" Then
      continue = MsgBox("Do you want to Continue to OE Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
    Oldfilename = TheFile

    ' Separate the file path and file name
      BS = InStrRev(Oldfilename, "\")
      Oldfilepath = Left(Oldfilename, BS)
      Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
    
    ' Add the CSV extension to the file name
      Dot = InStr(1, Newfilename, ".")
      Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
    
    ' Rename the file
      Name Oldfilename As Newfilename

    Workbooks.Open Filename:=Newfilename
     
    Set OpenA = Workbooks(Workbooks.Count)
    
    NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
    Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File COGSSTMACCT3")
    If TheFile = "False" Then
    OpenA.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to OE Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
     Oldfilename = TheFile

    ' Separate the file path and file name
      BS = InStrRev(Oldfilename, "\")
      Oldfilepath = Left(Oldfilename, BS)
      Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
    
    ' Add the CSV extension to the file name
      Dot = InStr(1, Newfilename, ".")
      Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
    
    ' Rename the file
      Name Oldfilename As Newfilename

    Workbooks.Open Filename:=Newfilename
     
    Set OpenB = Workbooks(Workbooks.Count)
    
    NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
    Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File FRPMOIEDTL")
    If TheFile = "False" Then
    OpenA.Activate
    ActiveWindow.Close
    OpenB.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to OE Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
    Oldfilename = TheFile

    ' Separate the file path and file name
      BS = InStrRev(Oldfilename, "\")
      Oldfilepath = Left(Oldfilename, BS)
      Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
    
    ' Add the CSV extension to the file name
      Dot = InStr(1, Newfilename, ".")
      Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
    
    ' Rename the file
      Name Oldfilename As Newfilename

    Workbooks.Open Filename:=Newfilename
     
    Set OpenC = Workbooks(Workbooks.Count)
    
    ChDrive "E:\"
    ChDir "E:\2007 Work\Analytical Review\Income Statement"

    TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Income Statement Review File")
    If TheFile2 = "False" Then
    OpenA.Activate
    ActiveWindow.Close
    OpenB.Activate
    ActiveWindow.Close
    OpenC.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to OE Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
    Workbooks.Open Filename:=TheFile2
    Set OpenD = Workbooks(Workbooks.Count)
    
    OpenD.Activate
    Sheets("Dload").Select
    Range("A1:Z700").Select
    Selection.ClearContents
    OpenA.Activate
    Rows("1:50").Select
    Selection.Copy
    OpenD.Activate
    Sheets("Dload").Select
    Rows("1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    OpenB.Activate
    Rows("1:130").Select
    Selection.Copy
    OpenD.Activate
    Sheets("Dload").Select
    Rows("51").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    OpenC.Activate
    Rows("1:130").Select
    Selection.Copy
    OpenD.Activate
    Sheets("Dload").Select
    Rows("195").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    OpenA.Activate
    ActiveWindow.Close
    OpenB.Activate
    ActiveWindow.Close
    OpenC.Activate
    ActiveWindow.Close
    Workbooks("Analytical Review Guide").Sheets(1).Range("vis").Value _
= IIf((Application.CountIf(OpenD.Sheets("Validation").Range("C3:F3"), "OK") = 4), "OK", "Error")
    OpenD.Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    
    Range("mis").FormulaR1C1 = "X"
    Application.ScreenUpdating = True
    Application.ScreenUpdating = False
    
Oest:

 

[QuietRiot]

Sub macro1()

OpenA.Activate

Dim wsSheet As Worksheet, checksheet As String

ChDrive "E:\"
    ChDir "E:\2007 Work\Analytical Review\Balance Sheet"

    TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Balance Sheet Review File")
    If TheFile2 = "False" Then
    OpenA.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
    Workbooks.Open Filename:=TheFile2
    Set OpenB = Workbooks(Workbooks.Count)
    
recheck:
'-------------------------------------- your error checkah
    For Each wsSheet In Worksheets
        If wsSheet.Name = "Dload" Then checksheet = "Yes"
    Next wsSheet
 '-------------------------------------------------------------------   
    If checksheet <> "Yes" Then
    
    continue2 = MsgBox("Dload tab missing. Do you want to check file and try again?", _
                    vbInformation + vbYesNo, _
                    "Check File?")
            If continue2 = 6 Then
                dlganswer = Application.Dialogs(xlDialogOpen).Show
                    If dlganswer = False Then Exit Sub
            Else
            If continue2 = 7 Then
            
            continue3 = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

                    If continue3 = 6 Then GoTo IncSt
                    If continue3 = 7 Then Exit Sub
                    
            End If
            GoTo recheck

    Else
    OpenA.Activate
    Cells.Select
    Selection.Copy
    OpenB.Activate
    Sheets("Dload").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    '------------- add code
    
    
    End If
    End Sub

 

[erock24]

Got It!!!!! with a little tweeking to your last post I think I nailed it so that I can re-use it over and over again throughout my macro.
I put this at the very top:

Dim continue&
Dim wsSheet As Worksheet, checksheet As String

then this in the body:

  Set OpenA = Workbooks(Workbooks.Count)
recheckBS:
    ChDrive "E:\"
    ChDir "G:\2007 Work\Analytical Review\Balance Sheet"

    TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Balance Sheet Review File")
    If TheFile2 = "False" Then
    OpenA.Activate
    ActiveWindow.Close
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
    Workbooks.Open Filename:=TheFile2
    Set OpenB = Workbooks(Workbooks.Count)
'-------------------------------------- your error checkah
    For Each wsSheet In Worksheets
        If wsSheet.Name = "Dload" Then checksheet = "Yes"
    Next wsSheet
'-------------------------------------------------------------------
    If checksheet <> "Yes" Then
    OpenB.Activate
    ActiveWindow.Close
    continue = MsgBox("Dload tab missing. Do you want to check file and try again?", _
                    vbInformation + vbYesNo, _
                    "Check File?")
            If continue = 6 Then GoTo recheckBS
            If continue = 7 Then
                OpenA.Activate
                ActiveWindow.Close
            continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

                    If continue = 6 Then GoTo IncSt
                    If continue = 7 Then Exit Sub
                        End If
                        End If
    OpenA.Activate
    Cells.Select
    Selection.Copy
    OpenB.Activate
    Sheets("Dload").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    OpenA.Activate
    ActiveWindow.Close
    Workbooks("Analytical Review Guide").Sheets(1).Range("vbs").Value _
= IIf((Application.CountIf(OpenB.Sheets("Validation").Range("D3:E3"), "OK") = 2), "OK", "Error")
    OpenB.Activate
    ActiveWorkbook.Save
    ActiveWindow.Close