How to add a else statement to code

[wao5012]

Hello all,

I am using the below code to copy specific cell values and paste the entire row into a new worksheet. I am having trouble finishing the code. After the last ElseIf statement i want to add another ElseIf or Else (not sure which one i would use) that will move the rest of the data into its own worksheet.

Does anyone know how to add this statement into the code below?

Sub CopyPasteRows()
a = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a


If Worksheets("Sheet2").Cells(i, 14).Value = "Wc" Then


Worksheets("Sheet2").Rows(i).Copy
Worksheets("Sheet3").Activate
b = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet3").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet2").Activate

ElseIf Worksheets("Sheet2").Cells(i, 14).Value = "ETOF" Then
Worksheets("Sheet2").Rows(i).Copy
Worksheets("Sheet3").Activate
b = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet3").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet2").Activate

End If

Next


Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet2").Cells(1, 1).Select






End Sub

 

[Domenic]

If you have a third condition, you would use ElseIf, like this...

    If Range("A1").Value = "x" Then
        'etc
        '
        '
    ElseIf Range("A1").Value = "y" Then
        'etc
        '
        '
    ElseIf Range("A1").Value = "z" Then
        'etc
        '
        '
    End If

Otherwise, to execute lines of code when the two conditions are not met, you would use Else, like this...

    If Range("A1").Value = "x" Then
        'etc
        '
        '
    ElseIf Range("A1").Value = "y" Then
        'etc
        '
        '
    Else
        'etc
        '
        '
    End If

Hope this helps!

 

[wao5012]

If you look at the code i posted you see i am splitting two cell values onto one worksheet i have a total of 10 values in that column I want the above two values on one worksheet (the code does this currently) then i want the rest of the data (8 other values) all on one in a different worksheet. How would i write the ELSE statement in the code.

Else ="every other value"

Code:
If Range("A1").Value = "x" Then
'etc
'
'
ElseIf Range("A1").Value = "y" Then
'etc
'
'
Else
'etc
'
'
End If
Hope this helps!

 

[Domenic]

In that case, use Else to copy rows that don't meet your conditions to another worksheet. But the method you're using is very inefficient. Try using the AutoFilter instead. First filter the data for rows that meet the two conditions, and copy them to your destination worksheet. Then filter the data again for rows that don't meet the two criteria, and copy them to your other destination worksheet. Should be much more efficient.

 

[wao5012]

Ok i will look into that. However, the file gets completed and produces what i want but it still says it is failing because of the below line. Do you know why this would do that?

The error i am receiving "Subscript out of Range"

ThisWorkbook.Worksheets("Sheet2").Cells(1, 1).Select

 

[Domenic]

ThisWorkbook refers to the workbook running the code. Therefore, the error suggests that the workbook running the code does not contain a sheet called "Sheet2". Is this the case? Maybe you meant to refer to the active workbook?

Also, in that particular line, the Select method of the Range object will fail if Sheet2 is not the active sheet. You'll get the run time error "Select method of range class failed".