Solved Compile Error, Else without If. Copy rows to another sheet.

February 11, 2019 at 11:20:22
Specs: Windows 10
I have merged two different codes together. The first section of code is used to cut and paste rows from one sheet and move them to a table on a seperate sheet.The second part of my code is to take rows of data and COPY them to a separate spreadsheet based on what is selected in the drop down menu in Column 4. This second part of the code will copy items to a spreadsheet in a seperate tab named 'KK To Do' when "Move to KK" is selected from the drop down menu. I would like to add more to this code so I can do the same for 3 others which would be:

Move to "JM To Do" when "Move to JM" is selected
Move to "CQ To Do" when "Move to CQ" is selected
Move to "BM To Do" when "Move to BM" is selected

Here is my current code

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 6 Then
    If Target = "Yes" Then
        Set Tbl = Sheets("Completed Tasks").ListObjects("Table1")
        
'Add a blank row, count rows based on Column 6
       Tbl.ListRows.Add
         lastTblRow = Tbl.Range.Rows.Count
         
'Copy/Paste Target in next-to-last row
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(lastTblRow - 1, 1)

'Delete Target Row
        Application.EnableEvents = False
          Target.EntireRow.Delete
        Application.EnableEvents = True
   End If

     ElseIf Target.Column = 4 Then
          If Target = "Move to KK" Then
       
     Application.EnableEvents = False
           nxtRow = Sheets("KK To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
     
     Target.EntireRow.Copy _
         Destination:=Sheets("KK To Do").Range("A" & nxtRow)
End If
     Application.EnableEvents = True

End If
End Sub

The code works great for the KK To Do. I tried to copy and paste that section of code and change the KK to JS, but when I try to type anything on the spreadsheet it gives me a "Compile Error, Else without If" on this line...

ElseIf Target.Column = 4 Then

Any suggestions?? I can provide additional information if needed!

Please note: I originally posted this at the end of my last question(Automatically Move Rows Into A Table On Another Sheet), but felt I should post this separately as it pertains to different information.


See More: Compile Error, Else without If. Copy rows to another sheet.

Report •

#1
February 12, 2019 at 05:32:50
✔ Best Answer
Try this

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = 6 Then
    If Target = "Yes" Then
        Set Tbl = Sheets("Completed Tasks").ListObjects("Table1")
        
'Add a blank row, count rows based on Column 6
       Tbl.ListRows.Add
         lastTblRow = Tbl.Range.Rows.Count
         
'Copy/Paste Target in next-to-last row
        Target.EntireRow.Copy _
            Destination:=Tbl.Range(lastTblRow - 1, 1)

'Delete Target Row
        Application.EnableEvents = False
          Target.EntireRow.Delete
        Application.EnableEvents = True
   End If

    ElseIf Target.Column = 4 Then
    
        If Target = "Move to KK" Then
           
            Application.EnableEvents = False
            nxtRow = Sheets("KK To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
         
            Target.EntireRow.Copy _
                Destination:=Sheets("KK To Do").Range("A" & nxtRow)
                
        ElseIf Target = "JM To Do" Then
           
            Application.EnableEvents = False
            nxtRow = Sheets("JM To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
         
            Target.EntireRow.Copy _
                Destination:=Sheets("JM To Do").Range("A" & nxtRow)
                
        ElseIf Target = "CQ To Do" Then
           
            Application.EnableEvents = False
            nxtRow = Sheets("CQ To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
         
            Target.EntireRow.Copy _
                Destination:=Sheets("CQ To Do").Range("A" & nxtRow)
        
        ElseIf Target = "BM To Do" Then
           
            Application.EnableEvents = False
            nxtRow = Sheets("BM To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
         
            Target.EntireRow.Copy _
                Destination:=Sheets("BM To Do").Range("A" & nxtRow)
    End If
     Application.EnableEvents = True

End If
End Sub



Report •

#2
February 12, 2019 at 07:08:14
I have not dug deep into this code, but I noticed something that I think should be addressed:

There are 3 issues that I see with the following segment.

        Application.EnableEvents = False
            nxtRow = Sheets("BM To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
                Target.EntireRow.Copy _
                Destination:=Sheets("BM To Do").Range("A" & nxtRow)
          End If
        Application.EnableEvents = True

My first issue relates to the order of the instructions.
My second issue relates to the lack of an error handling routine.
My third issue relates to the use of the EnableEvents instructions in this situation. Is it really even needed?

My first 2 issues are general points that I would like to make related to use of the Application.EnableEvents instructions. The 3rd issue is directly related to this project.

Issue #1:

Since disabling events is a pretty major thing to do, I like to use that instruction carefully. If I need to disable events, I prefer to disable them, do only what I need to do with Events disabled and then enable them. There is less chance for any extraneous instructions to cause an error with Events disabled, perhaps leaving them disabled.

So, just as an example of what I mean, I would rather see something like this:

            
  nxtRow = Sheets("BM To Do").Range("D" & Rows.Count).End(xlUp).Row + 1
      
       Application.EnableEvents = False
             Target.EntireRow.Copy _
                Destination:=Sheets("BM To Do").Range("A" & nxtRow)
        Application.EnableEvents = True

    End If
    

Issue #2:

There is no error handling for this situation. Let's say the Copy/Paste instruction fails, e.g. maybe the BM To Do sheet doesn't exist or maybe the Range("A" & nxtRow) fails for some reason. The code will crash and Events will remain disabled until you do something to turn them back on. (Restart Excel, run some other code that turns them back on, etc.) Keep in mind that EnableEvents is an Application level instruction. Once Events are disabled by that instruction, they are disabled for all sheets and all workbooks within that instance of Excel. If there is any chance that an error can occur with Events disabled then there should an Error Handling routine that Enables them again before the code exits.

Issue #3:

Take a close look at the instruction in between the EnableEvents False/True toggle instructions.

The code is copying a Row. Copying is not a change to the sheet in which the copying is being done. Unless there is Worksheet_Change code running in the Destination sheet, I see no reason to disable Events before copying the Row.

As I said, Issues 1 & 2 are just general issues related to using the EnableEvents instruction, but in this case, I don't think it's even needed, so why risk it?

message edited by DerbyDad03


Report •
Related Solutions


Ask Question