Capturing data from one sheet and transferring the row to another sheet

[AKG1983]

Hi I hope someone can help me as I am in a right flap about this.

I have a spreadsheet that holds information about different company's and items they have returned, all this information is on one sheet. what i need is a code, formula, macro (basically anything that will work) to be able to look at the entire sheet of information and move the relevant rows for each seperate company to a different sheet.

A RMA 123 Orange 01.06.2011 02.06.2011 10.06.2011 11.06.2011 Orange
B RMA 234 blue 15.06.2011 16.06.2011 24.06.2011 25.06.2011 blue
C RMA 345 red 15.06.2011 16.06.2011 24.06.2011 25.06.2011 red
C RMA 456 yellow 18.06.2011 19.06.2011 27.06.2011 28.06.2011 yellow
D RMA 567 white 20.06.2011 21.06.2011 29.06.2011 30.06.2011 white
F RMA 678 white 20.06.2011 21.06.2011 29.06.2011 30.06.2011 white
F RMA 789 red 20.06.2011 21.06.2011 29.06.2011 30.06.2011 red
A RMA 891 yellow 30.06.2011 01.07.2011 09.07.2011 10.07.2011 yellow
A RMA 198 Orange 01.07.2011 02.07.2011 10.07.2011 11.07.2011 Orange
B RMA 987 blue 05.07.2011 06.07.2011 14.07.2011 15.07.2011 blue
E RMA 876 red 10.07.2011 11.07.2011 19.07.2011 19.07.2011 red
E RMA 765 yellow 15.07.2011 16.07.2011 24.07.2011 25.07.2011 yellow
E RMA 654 white 15.07.2011 16.07.2011 24.07.2011 25.07.2011 white
E RMA 543 white 15.07.2011 16.07.2011 24.07.2011 25.07.2011 white
B RMA 432 red 15.07.2011 16.07.2011 24.07.2011 25.07.2011 red
C RMA 321 yellow 20.07.2011 21.07.2011 29.07.2011 30.07.2011 yellow


So for Company A, that I have highlighted in blue, I would need all the information that is in that row to be copied, as it is seen in the main spreadsheed to the relevant sheet for that specific company, without having to copy and past every time it is entered into the main sheet. I guess I need my seperate sheets to look at the main sheet pick out their company from the first column and then pick up all the data in the rest of the row.

Does anyone know how to do this? Any help would be greatly appreciated as I need this for a presentation that i have to do tomorrow .

Thanking you all in advance

[Mordred]

Hi AKG1983 and welcome to the forum. Perhaps it would be best if you could upload a mock workbook with mock data in it with your desired outcomes so that we can help you in time for your presentation. Especially with regards to how to set the criteria for what rows will be copied.

Regards

[AKG1983]

Hi Mordred

Thank you for you reply and your kind welcome.

I will try and upload my mock spredsheet for you to have a look at. Not totally sure how to do this as never used it before but will give it a go.

I hope all the information that I provided helps. If you need anything else just let me know.

Thank you.

AKG1983

[Mordred]

Hi AKG1983,

I have a workbook to get things going for you. The only problem I see thus far with it is once the data is copied from Sheets("All Information"), it can be moved again causing duplicates. I should be able to fix that for you but I have meetings to attend to for the next little while so I have to stop for a bit. Anyhow, check it out.

[AKG1983]

Hi Mordred

thank you for your reply, unfortunately i cannot open the attachment it is telling me to contact the administrator????

could you re-attach it for me please.

Many thanks.

[Mordred]

Sure, we'll try again!

[Mordred]

If that doesn't work, throw the following code into a module and attach it to a button on your "All Information" sheet.

Please Login or Register  to view this content.

[Mordred]

And I still have two minutes to get to my meeting. Try this code, if there are no new entries, the code won't move any information.

Please Login or Register  to view this content.

[AKG1983]

Hi Mordred

That is amazing i see what you mean about the duplicates and will attempt to do as you suggested with the macro for the 'all informaton' sheet (not 100% sure how to do it though).

Thank you for all your help with this, it is greatly appreciated.

Many thanks
AKG1983

[Mordred]

Hi AKG,
I think I fixed the duplicates problem with post # 8. If not, let me know as my meeting was cancelled.

[Mordred]

(not 100% sure how to do it though).
AKG1983

Do you know where to insert the code?

[AKG1983]

not a clue. sorry i am totally useless and using a lot of your time

[Mordred]

No need to apologize and I doubt you are useless, you just don't know (yet).
1) Press Alt & F11 to open the Visual Basic Editor (VBE)
2) If you are going to add the code to your own workbook, click:
Insert > Module
3)Copy the code from this site and paste it into the new module
I'll let you know about how to incorporate the button if you need it but for now, lets get your code into the module.

[AKG1983]

Do I keep the module that is there already or do i delete or change it? When creating the new module it seems that you can put it anywhere. do i put it on one of the sub folders or the main one?

again thank you so much for all this.

[Mordred]

If it is the workbook that I uploaded for you then you can delete the old procedure and replace it with the new one. If the workbook is yours and there is already code in it, don't delete it! I'm not sure what you mean by put it in one of the sub folders. Just place the code in the new module. If you double click (in the project window on the left side of the VBE) Sheet1(All Information) you'll see some code there for button1_Click event, that runs the code when the button is pressed.

[AKG1983]

everytime i add a new module it changes the other one to say the same thing. is that meant to happen?

[Mordred]

everytime i add a new module it changes the other one to say the same thing. is that meant to happen?

If you add another module, the module number should increment ie: Module1, Module2, and etc... You will be thrown errors if you add the same procedure into many modules because of ambiguous names so just add the procedure once. It doesn't matter whether the procedure goes into Module1, or Module2, or etc. as long as it is only pasted into one of them.

[AKG1983]

right done all of that and when i try and run te macro it comes up with a run time error saying 'Method 'Range' of object' _worksheet'failed

any ideas?

[Mordred]

Save your workbook and re-upload it for me and I'll figure it out for you.

[AKG1983]

thank you, i appreciate this very much.

[Mordred]

It's close AKG but the catching of duplicates is evading me. I'll get it but it may take me a few minutes.

[Mordred]

I'm still trying to figure this out but I may be a little over my own head on this so I've asked for additional help.

[AKG1983]

thank you very much for your time on this, you are being amazingly helpful

[tigeravatar]

AKG1983,

Attached is a modified version of your sample workbook. It has a button titled "Split Data" which is assigned to the following macro:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[AKG1983]

You are amazing thank you so much for all your help x

[Mordred]

If you feel that your problem has been solved, please mark your thread as solved. Also, if you feel that any member has been helpful to you then don't be shy and tap their scales (located at the top right of each post).

Regards: