Move Entire Row To Different Worksheet Based On Exact Cell Value

[st3v3y]

Hi there

I apologise if this has been solved but I have searched and can't find the answer. I have 'stitched' bits of code together and feel so close to solving this. I have this working but it won't 'loop' and therefore only completes the task for individual rows.

In a nutshell I search Sheet 1, Column Q, Row 3 onwards for a set value and then move the entire row to Sheet 3, Row 3 onwards. My code works but I have to run it 10 times if there are 10 rows that contain the value of 1 in them.

As you can probably tell, I'm useless at VBA!

Here is my code....

Please Login or Register  to view this content.

If I want to search for a value of 2 and move to sheet 5 then I have to change theses value and keep running again.

I would like to add this to my code but don't know how to?

Please Login or Register  to view this content.

I can add a sample sheet if required but basically I'm just trying to look at everything in column Q from row 3 onwards so wasn't sure if it was needed as the rest of the row data is irrelevant.

Thanks for reading this.
Steve

[st3v3y]

Thanks for heads up Fotis!

I wondered how everyone did that. Looks better than Bold too!

Steve

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[st3v3y]

Hi John and thanks for the quick reply.

That did manage to copy the desired rows from Sheet 1 but did not delete them.

I'll try and manipulate it. My initial attempt failed though.

[JOHN H. DAVIS]

This should do the deleting. You have to delete from the bottom up.

Please Login or Register  to view this content.

[st3v3y]

Hi John

Sorry but replacing the previous end to your code with the latest one did not make any difference. Nothing gets deleted.

Thanks
Steve

[JOHN H. DAVIS]

Can you attach a sample where it doesn't work? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[st3v3y]

Yeah of course. I just need to get rid of some sensitive information first.

[st3v3y]

I've deleted everything I've had to.

st3v3y.xlsx

[st3v3y]

Hi John

I just tried the code on the Workbook I posted above and it worked!?

I will now try to implement this into my original workbook and figure out why it didn't work the first time around.

I will update this thread once I've done that but for now, thank you.

Steve

[JOHN H. DAVIS]

I just tested it on the example you provided and it worked for me? Here is the code I used?

Please Login or Register  to view this content.

[st3v3y]

Hi John

It still won't delete on the original sheet but that is ok because I can just create new sheets and it works fine. I have also managed to add other Cell Values and moved them to different sheets so this is great for me!

However if I have a cell value that is UK1 instead of 1 then the above code ignores this. Can this be added to your code?

Thanks
Steve

[JOHN H. DAVIS]

You can add another case statement:

Please Login or Register  to view this content.

Place before your End Select line.

[JOHN H. DAVIS]

Also note: Text values need to be enclosed with quotations as above.

[st3v3y]

I was setting the value as UK1 and not "UK1". All works perfectly.

Thanks a lot John

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.