Move data to specific sheets based on value against a list

Is there a way by using a macro i can move data from a dump sheet into a specific sheet

For Example:

If in Sheet1 i have my data in Columns A - D

In Columns E - F i have a list containing store numbers and names

I want a macro that looks at the data in Columns A - D and then think ok A1 has Number 20 in so it will check the list in E - F and see what store number 20 corresponds too

Then move that information in A1 - D1 to the tab with that store name

Then repeat down to the end moving each line in to its own specified sheet

All sheets have been created in the workbook and there is around 200 sheets

Thanks

Dave

Can you provide a sample with your layout and some dummy data?

Originally Posted by JOHN H. DAVIS

Can you provide a sample with your layout and some dummy data?

Hi John

I have attached a dummy copy of the spreadsheet ...

Auto Email Dummy.zip

Basically I will post the data into the Dump Sheet which want a macro to move the data to a specific tab

E.G.: Ship to Customer 1 in the dump sheet is the value I want to separate the data by .. If we look are the Store List tab 1 = Keighley and 2 = Egremont

I want the macro to look in that column and match it to the store list then move it to the relevant stores tab for that number and name

Hope this makes sense ?

Thanks

Dave

Maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

As far as i can see nothing happened

Thanks

Dave

Did you run it from a standard module? Did you check the Sheets it was supposed to copy too. When I tested it, it worked for me?

Originally Posted by JOHN H. DAVIS

Did you run it from a standard module? Did you check the Sheets it was supposed to copy too. When I tested it, it worked for me?

Oh dear ... Rookie error ... I pasted the macro in the sheets object

Ok 2nd thing .. I am currently using just short of 200 tabs for all this data .. is there anyway i can make it so that if i hide all the sheets the macro can unhide the relevant sheets only then paste the data or vice versa ?

Thanks

Dave

Maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

I currently have all the sheets pre-hidden

When i ran this macro the sheet the data was moved to didn't unhide itself

Thanks

Dave

Try:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Try:

Please Login or Register  to view this content.

That works perfectly !!

Thanks John

The next part of this would be to setup a macro to automatically send the relevant sheet to the relevant store via the use of outlook email as you see the storelist tab contains email addresses for each store

Are you able to assist with this ? or would i need to create a new thread ?

Thanks

Dave

You're welcome. Glad to help out and thanks for the feedback. I'm afraid the outlook is not my cup of tea, since I have Lotus Notes. Please mark this thread as solved.

http://www.excelforum.com/forum-rule...rum-rules.html

Ok thanks for your help John