Move Row into new sheet based on cell value

[cheezy]

Hi Guys,

New here.

I have been searching the web and these forums and cannot find a specific solution.

I need write a VBA code that will move an entire row into a new sheet if the value of the last cell says "YES"
If the value is blank then Id like it to stay in the current sheet.
I would also like it if the cell wont allow any other value other than Yes to be typed.

I found this code here:

http://www.mrexcel.com/forum/excel-questions/397784-copy-move-delete-row-based-cell-value.html

THE VERY FIRST CODE REPLIED IN THAT THREAD.



I just cannot seem to get it to work. Simply copying and pasting that into the module doesn't work. I am not very good at VBA and just started taking a course on VBA.
Id like to jump into this problem asap as I need it for work.





Basically:

Columns A:E will have values. In Column E I would like to type in Yes if completed.
I would like the Macro to run through all of Column E and if the cell value is "YES" then I want it to cut the entire row and paste into a new sheet.
In the new sheet I would like it to be pasted into the next available row.

Also can I create this with a ActiveX control button? I would like to have a button that I can click at the end of my work which will run the Macro in sheet 1.

Thanks a lot guys an help is appreciated!

 

[aron1987]

There is;

BNE Receivals
BNE Trimming
BNE Clean Room
BNE Packing
BNE Daily Fresh - Assembly
BNE Daily Fresh - Prep
BNE Despatch
BNE Hygiene

..and I wanted each to show on a separate tab.

Thanks for your help Michael!

 

[aron1987]

The other question I have which will sound silly

Is where do I paste the code?

Thanks
Aron

 

[Michael M]

Are all those sheets already created ???

 

[aron1987]

They haven't been created yet - can basically start from scratch if required

Thanks
Aron

 

[Michael M]

Pretty rough, but this assumes the following:
1. the sheet names to be created are on a sheet called "Name List" in column "A"
2. The master list starts at row 2
3. each time the code is run it refreshes ALL sheets by creating new ones

Sub MM1()
Dim x As Variant, wSh As Worksheet, r As Long, lastrow2 As Long, lastrow As Long
Dim ws As Worksheet
Set wSh = Worksheets("Name List")
Set ML = Worksheets("Master List")
wSh.Activate
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Master List" And ws.Name <> "Name List" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
End If
Next ws
For Each x In wSh.Range("A1:A8")
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = x.Value
Next x
ML.Activate
lastrow = ML.Cells(Rows.Count, "A").End(xlUp).Row
    For r = 2 To lastrow
    Select Case Range("A" & r).Value
         Case Is = "BNE Receivals"
         lastrow2 = Worksheets("BNE Receivals").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Receivals").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Trimming"
         lastrow2 = Worksheets("BNE Trimming").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Trimming").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Clean Room"
         lastrow2 = Worksheets("BNE Clean Room").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Clean Room").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Packing"
         lastrow2 = Worksheets("BNE Packing").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Packing").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Daily Fresh - Assembly"
         lastrow2 = Worksheets("BNE Daily Fresh - Assembly").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Daily Fresh - Assembly").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Daily Fresh - Prep"
         lastrow2 = Worksheets("BNE Daily Fresh - Prep").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Daily Fresh - Prep").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Despatch"
         lastrow2 = Worksheets("BNE Despatch").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Despatch").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
         Case Is = "BNE Hygiene"
         lastrow2 = Worksheets("BNE Hygiene").Cells(Rows.Count, "A").End(xlUp).Row
         Rows(r).Copy Worksheets("BNE Hygiene").Range("A" & lastrow2 + 1)
         lastrow2 = lastrow2 + 1
    End Select
    Next r
Application.ScreenUpdating = True
End Sub

 

[aron1987]

Could you please confirm where I paste this code?

Thanks heaps!

 

[Michael M]

Follow these steps


Copy the code that I posted
Open the workbook in which you want to add the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste



To run the code:
On the Excel Ribbon, click the View tab
At the far right, click Macros
Select a macro in the list, and click the Run button

 

[aron1987]

Seem to be having some trouble - i'll see if I can sort it out thanks a lot so far for your help

 

[aron1987]

May be a long shot ..but could you send me the excel file with this working?

 

[Michael M]

What seems to be the problem ??
Did you read these initial instructions ?

Pretty rough, but this assumes the following:

1. the sheet names to be created are on a sheet called "Name List" in column "A"
2. The master list starts at row 2
3. each time the code is run it refreshes ALL sheets by creating new ones