copying data from one sheet to another based on selection

I'm trying to create a workbook that will sort data from multiple sheets and place the relevant info into another sheet based on user input. there will be multiple sheets that are exactly the same name A1 - A10 for example. within those sheets there is a column that allows user to select from pull down list 2 options, in or out. they in also type in a comment in the adjacent column. In the end I want to have a separate sheet named "in" and one named "out" that would sort through sheets A1 - A10 and pull the comments when the word "in" or "out" is selected from the pulled down list. also would like to know which sheet A1 - A10 the comment was pulled from.

any suggestions? please let me know if more information needed to provide possible solution.

Originally Posted by kevinspruell

I'm trying to create a workbook that will sort data from multiple sheets and place the relevant info into another sheet based on user input. there will be multiple sheets that are exactly the same name A1 - A10 for example. within those sheets there is a column that allows user to select from pull down list 2 options, in or out. they in also type in a comment in the adjacent column. In the end I want to have a separate sheet named "in" and one named "out" that would sort through sheets A1 - A10 and pull the comments when the word "in" or "out" is selected from the pulled down list. also would like to know which sheet A1 - A10 the comment was pulled from.

any suggestions? please let me know if more information needed to provide possible solution.

First you need to create a loop that will loop through the ten sheets. There's a few ways of doing this. See here for one method: http://www.vbaexpress.com/kb/getarticle.php?kb_id=390. I used another way.

I had to make a few assumptions but you should be able to fix them if they're wrong.

Please Login or Register  to view this content.

I didn't actually run this so it could be filled with errors but see how you get on with that much

thanks this will probably do what i want, but i have not idea how to get it into and work in my file. it needs modification. attached is a copy of my file if that helps

Originally Posted by kevinspruell

thanks this will probably do what i want, but i have not idea how to get it into and work in my file. it needs modification. attached is a copy of my file if that helps

Sorry, I didn't see this until now. If it's not too late...

If you open up the VBA editor (in excel press ALT+F11), go to Insert > Module. Paste that code into the page that appears. Then to run it put your cursor anywhere in the code and press F5 or click the run button (the little green play button) up top.