davidbrent
New Member
- Joined
- Feb 25, 2016
- Messages
- 6
Hi All,
I have a sheet with a drop down selection box which depending upon selection needs the entire row to copy, past (into other sheet) and delete original sheet row. I have this working for one selection but when i repeat my code for "Selection2" it doesn't work and i'm stuck!
Below code and example sheet attached so i can hopefully stop banging my head off my table!
I have a sheet with a drop down selection box which depending upon selection needs the entire row to copy, past (into other sheet) and delete original sheet row. I have this working for one selection but when i repeat my code for "Selection2" it doesn't work and i'm stuck!
Below code and example sheet attached so i can hopefully stop banging my head off my table!
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To a
If Worksheets("Sheet1").Cells(I, 1).Value = "Selection1" Then
Worksheets("Sheet1").Rows(I).Cut
Worksheets("Sheet2 ").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate
End If
Next
'This is where i need the above code to repeat for "Selection2" and paste into "Sheet3", then "Selection3 into "Sheet4" etc etc'
For I = 2 To a
If Worksheets("Sheet1").Cells(I, 1).Value = "" Then
Rows(I).Delete
End If
Next
End Sub