End if trouble

[jmazorra]

Hello folks:

I wrote the code below, but I am having trouble with end if statements at the end. I keep getting: Compile Error: Expected End With

I know this is a simple fix, but I am still feeling my way through VBA.

Sub copyandpaste()
Dim sourceFile As String
Dim firstDestFile As String
Dim secondDestFile As String
Dim thirdDestFile As String
Dim fourthDestFile As String
Dim fifthDestFile As String
sourceFile = "2011.1004.Salary Survey Template.xlsm"
firstDestFile = Workbooks(sourceFile).Path & "\byemployee.csv"
secondDestFile = Workbooks(sourceFile).Path & "\byposition.csv"
thirdDestFile = Workbooks(sourceFile).Path & "\bydepartment.csv"
fourthDestFile = Workbooks(sourceFile).Path & "\byband.csv"
fifthDestFile = Workbooks(sourceFile).Path & "\status report.xls"
If Len(Dir(firstDestFile)) = 0 Then
MsgBox firstDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
ThisWorkbook.Close savechanges:=False
Else

With Workbooks.Open(firstDestFile)
If Len(Dir(firstDestFile)) > 0 Then
.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byemployee").Range("A1")
.Close savechanges:=False
End If
If Len(Dir(secondDestFile)) = 0 Then
MsgBox secondDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
ThisWorkbook.Close savechanges:=False
Else

With Workbooks.Open(secondDestFile)
If Len(Dir(secondDestFile)) > 0 Then
.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byposition").Range("A1")
.Close savechanges:=False
End If
If Len(Dir(thirdDestFile)) = 0 Then
MsgBox thirdDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
ThisWorkbook.Close savechanges:=False
Else
With Workbooks.Open(thirdDestFile)
If Len(Dir(thirdDestFile)) > 0 Then
.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("bydepartment").Range("A1")
.Close savechanges:=False
End If
If Len(Dir(fourthDestFile)) = 0 Then
MsgBox fourthDestFile & " does not exists in directory. Press OK to Continue"

Else

With Workbooks.Open(fourthDestFile)
If Len(Dir(fourthDestFile)) > 0 Then
.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byband").Range("A1")
.Close savechanges:=False
End If
If Len(Dir(fifthDestFile)) = 0 Then
MsgBox fourthDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."

Else

With Workbooks.Open(fifthDestFile)
If Len(Dir(fifthDestFile)) > 0 Then
.Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("status report").Range("A1")
.Close savechanges:=False
End If
End With
End If

End Sub

 

[jaybee3]

From first glance you have only closed one of your End Withs and hardly any of your ifs. Indentation would help you.

Sub copyandpaste()
Dim sourceFile As String
Dim firstDestFile As String
Dim secondDestFile As String
Dim thirdDestFile As String
Dim fourthDestFile As String
Dim fifthDestFile As String
sourceFile = "2011.1004.Salary Survey Template.xlsm"
firstDestFile = Workbooks(sourceFile).Path & "\byemployee.csv"
secondDestFile = Workbooks(sourceFile).Path & "\byposition.csv"
thirdDestFile = Workbooks(sourceFile).Path & "\bydepartment.csv"
fourthDestFile = Workbooks(sourceFile).Path & "\byband.csv"
fifthDestFile = Workbooks(sourceFile).Path & "\status report.xls"
If Len(Dir(firstDestFile)) = 0 Then
    MsgBox firstDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
    ThisWorkbook.Close savechanges:=False
Else
    With Workbooks.Open(firstDestFile)
        If Len(Dir(firstDestFile)) > 0 Then
           .Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byemployee").Range("A1")
           .Close savechanges:=False
        End If
    End With
    If Len(Dir(secondDestFile)) = 0 Then
        MsgBox secondDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
        ThisWorkbook.Close savechanges:=False
    Else
        With Workbooks.Open(secondDestFile)
            If Len(Dir(secondDestFile)) > 0 Then
                .Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byposition").Range("A1")
                .Close savechanges:=False
            End If
        End With
        If Len(Dir(thirdDestFile)) = 0 Then
            MsgBox thirdDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
            ThisWorkbook.Close savechanges:=False
        Else
            With Workbooks.Open(thirdDestFile)
                If Len(Dir(thirdDestFile)) > 0 Then
                    .Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("bydepartment").Range("A1")
                    .Close savechanges:=False
                End If
            End With
            If Len(Dir(fourthDestFile)) = 0 Then
                MsgBox fourthDestFile & " does not exists in directory. Press OK to Continue"
            Else
                With Workbooks.Open(fourthDestFile)
                    If Len(Dir(fourthDestFile)) > 0 Then
                        .Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("byband").Range("A1")
                        .Close savechanges:=False
                    End If
                End With
                If Len(Dir(fifthDestFile)) = 0 Then
                    MsgBox fourthDestFile & " does not exists in directory. Check to make sure the file exists or has been named correctly."
                Else
                    With Workbooks.Open(fifthDestFile)
                        If Len(Dir(fifthDestFile)) > 0 Then
                            .Worksheets(1).Cells.Copy Workbooks(sourceFile).Worksheets("status report").Range("A1")
                            .Close savechanges:=False
                        End If
                    End With
                End If
            End If
        End If
    End If
End If
End Sub

Try that

 

[jmazorra]

Thank you, I did not realize that I had to write so many End if statemnets and the end.

Thanks again.

 

[jaybee3]

No probs. As far as I know every If, With, or Loop needs an End. (Except single line Ifs) AFAIK.