End if without Block If

[J-Richards]

Hi All,

I'm really new to this and have been trying to code. I'm trying to select a data range and copy it into a file save both files, clear the contents of the original selection and save and close that workbook too.

The below did work:

Sub CommandButton1_Click()
Application.ScreenUpdating = False
If Workbooks("Support Workload Tracker").Worksheets("Data Capture").Range("B2") <> "" Then
Dim AnswerYes As String
Dim AnswerNo As String
AnswerYes = MsgBox("Are you finishing for the day?", vbQuestion + vbYesNo, "End Of Day")
If AnswerYes = vbNo Then
MsgBox "Action Cancelled"
Cancel = True

Else

Workbooks("Support Workload Tracker").Activate
With Worksheets("Data Capture")
    LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
    .Range("A2:N" & LastRow).Copy
End With
Dim wb As Workbook
Filespec = "xxxxxxx(webaddress)xxxxxxx"
Set wb = Application.Workbooks.Open(Filename:=Filespec)
Workbooks("MASTER Support Workload Tracker").Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
For Each wb In Workbooks
wb.Save
Next wb
Workbooks("MASTER Support Workload Tracker").Close
End If
End If
Application.ScreenUpdating = True
End Sub

But when I added this after the "Workbooks("MASTER Support Workload Tracker").Close"

Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
Workbooks("Support Workload Tracker").Activate
With Worksheets("Data Capture")
    LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
    .Range("A2:N" & LastRow).ClearContents

It stopped working. Can anyone tell me why? And how to fix it.

For full clarity this is how the code looks currently and isn't working

Sub CommandButton1_Click()
Application.ScreenUpdating = False
If Workbooks("Support Workload Tracker").Worksheets("Data Capture").Range("B2") <> "" Then
Dim AnswerYes As String
Dim AnswerNo As String
AnswerYes = MsgBox("Are you finishing for the day?", vbQuestion + vbYesNo, "End Of Day")
If AnswerYes = vbNo Then
MsgBox "Action Cancelled"
Cancel = True

Else

Workbooks("Support Workload Tracker").Activate
With Worksheets("Data Capture")
    LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
    .Range("A2:N" & LastRow).Copy
End With
Dim wb As Workbook
Filespec = "xxxxxxxxxx(webaddress)xxxxxxxxx"
Set wb = Application.Workbooks.Open(Filename:=Filespec)
Workbooks("MASTER Support Workload Tracker").Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
For Each wb In Workbooks
wb.Save
Next wb
Workbooks("MASTER Support Workload Tracker").Close
Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
Workbooks("Support Workload Tracker").Activate
With Worksheets("Data Capture")
    LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
    .Range("A2:N" & LastRow).ClearContents
End If
End If
Application.ScreenUpdating = True
End Sub

[ByteMarks]

Try

'.....
With Worksheets("Data Capture")
    LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
    .Range("A2:N" & LastRow).ClearContents
End With
End If
End If
Application.ScreenUpdating = True
End Sub

[MatrixMan]

You will find it MUCH easier to read and find these sorts of problems if you indent lines properly, such as this:

Sub CommandButton1_Click()
    
    Application.ScreenUpdating = False
    
    If Workbooks("Support Workload Tracker").Worksheets("Data Capture").Range("B2") <> "" Then
    
        Dim AnswerYes As String
        Dim AnswerNo As String
        AnswerYes = MsgBox("Are you finishing for the day?", vbQuestion + vbYesNo, "End Of Day")
        If AnswerYes = vbNo Then
            MsgBox "Action Cancelled"
            Cancel = True
        Else
            Workbooks("Support Workload Tracker").Activate
            With Worksheets("Data Capture")
                LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
                .Range("A2:N" & LastRow).Copy
            End With
            
            Dim wb As Workbook
            Filespec = "xxxxxxxxxx(webaddress)xxxxxxxxx"
            Set wb = Application.Workbooks.Open(Filename:=Filespec)
            Workbooks("MASTER Support Workload Tracker").Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
            ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
            For Each wb In Workbooks
                wb.Save
            Next wb
            
            Workbooks("MASTER Support Workload Tracker").Close
            Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
            ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
            
            Workbooks("Support Workload Tracker").Activate
            With Worksheets("Data Capture")
                LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
                .Range("A2:N" & LastRow).ClearContents
            End With    'this is the one you missed.
        End If
    End If
    
    Application.ScreenUpdating = True
    
End Sub

I would also suggest you introduce some naming discipline to help read through your code by using 3-letter prefixes to identify the data type, such as

Dim strAnswerNo as String, strAnswerYes as String
Dim varExample as Variant
Dim dblAmount as Double
Dim rngData as Range

... and so on.

There are exceptions such as ws and wb (or wks and wkb) and rng and cel (e.g. For each cel in rng) as these are commonly used.

[J-Richards]

Amazing, thank you so much for your help.

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[J-Richards]

So this has worked for the most part but now I am receiving an object defined error. Can anyone help?

Application.ScreenUpdating = False
If Workbooks("Support Workload Tracker").Worksheets("Data Capture").Range("B2") <> "" Then
    Dim AnswerYes As String
    Dim AnswerNo As String
    AnswerYes = MsgBox("Are you finishing for the day?", vbQuestion + vbYesNo, "End Of Day")
    If AnswerYes = vbNo Then
        MsgBox "Action Cancelled"
        Cancel = True
    Else
        Workbooks("Support Workload Tracker").Activate
        With Worksheets("Data Capture")
            LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
            .Range("A2:N" & LastRow).Copy
        End With
        
        Dim wb As Workbook
        Filespec = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
        Set wb = Application.Workbooks.Open(Filename:=Filespec)
        Workbooks("MASTER Support Workload Tracker").Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
        For Each wb In Workbooks
            wb.Save
        Next wb
        Workbooks("MASTER Support Workload Tracker").Close
        Worksheets("Historical").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
        
        Workbooks("Support Workload Tracker").Activate
        With Worksheets("Data Capture")
            LastRow = .Cells(Rows.Count, "N").End(xlUp).Row
            .Range("A2:N" & LastRow).ClearContents
        End With
    End If
End If
Application.ScreenUpdating = True