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]

The sheet with the names / absences are on a sheet called "Master List"

Do I setup each of the other sheets for each of the areas (ie: BNE Trimming, BNE Packing etc?)

 

[Michael M]

No the code will do it for you IF...
As mentioned twice earlier.....
you created a new sheet called "Name List"
AND
put the names of the sheets required in column "A" of that sheet

 

[aron1987]

Getting some progress now..

It comes up with an error that dats "Run time error '9' Subscript out of range

 

[Michael M]

OK Are the sheet names to be created... EXACTLY the same as those in the code ???
Is "Master List" syntax correct ??
Is "Name List" syntax correct ??

 

[aron1987]

Seems to somewhat work now, its creating the sheets now - but not all the data is coming onto those sheets.

Some have none those that have any on them it's only pulling through the first line it see's

 

[Michael M]

Did you get my private mail ???

 

[aron1987]

It's working well now - geez your good with Excel!

Only two questions I have,

Is how to I have headings that stay there in row 1 and column widths set / stay the same?

 

[aron1987]

Private message replied.

 

[Michael M]

Once the code is in the VB editor, save it !
Then go back to the Master Sheet and select the developer tab, click on macros and select the macro from the list that appears and then select Run.


The code works fine for me and copies all data to the sheets except row 5 which is TRIM MID

 

[Wjiz]

Once the code is in the VB editor, save it !
Then go back to the Master Sheet and select the developer tab, click on macros and select the macro from the list that appears and then select Run.


The code works fine for me and copies all data to the sheets except row 5 which is TRIM MID

Hi There, I need some help with a similar problem. However is there a way to do this without using VBA? please thank you.