Need VBA to repeat for second additional requests but only works on first IF

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!

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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Is sheet1 column A dropdown boxes?
For instant, Dropdownbox in A1 value could be "Selection1","Selection2",...,"Selectionn"
then if A1 change value, row 1 will be deleted and copy to sheet2?
Why did you put the code in SelectionChange event? why not Change event, for column A only?
 
Upvote 0
Is sheet1 column A dropdown boxes?
For instant, Dropdownbox in A1 value could be "Selection1","Selection2",...,"Selectionn"
then if A1 change value, row 1 will be deleted and copy to sheet2?
Why did you put the code in SelectionChange event? why not Change event, for column A only?
Column A is drop down boxes, and depending upon selection, the row goes to the applicable sheet

The SelectionChange event was used as with my very basic VBA knowledge i adapted the code from and existing online search!
 
Upvote 0
Could you post image of: before and after?
Thank you for the assistance, but the code before and after is just me changing the sheet names and the selection name. The original author of the code only had one selection going to one sheet and i was hoping i could just copy and paste the code above multiple times for my different selections, but when i do, the code just runs through the first part then stops at any other selection. i.e. "Selection1" always does what i want, but the "selection2" doesn't
Could you post image of: before and after?
Test Sheet.xlsm
ABCDE
1SelectionInfo1Info2Info3Info4
2Please Select
3Please Select
4Please Select
5Please Select
6Please Select
7Please Select
8Please Select
9Please Select
10Please Select
11Please Select
12Please Select
Sheet1
Cells with Data Validation
CellAllowCriteria
A2:A12ListPlease Select, Selection1, Selection2, Selection3, Selection4
 
Upvote 0
This was answered in a different forum.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top