Import appropriate data to worksheet

[james598]

I have a workbook that contains an 'AllData' sheet. That sheet has 6 columns titled "category, keyword, bid, advertisers, search vol, online". Each category has its own worksheet and i want to put the data for each category onto the corresponding worksheet. I am having a difficult time describing this so I have attached a brief example. I have to do this for 145 categories so I would much prefer to do it automatically rather than manually

[james598]

Sorry forgot to attach

Thanks!
James

[jaslake]

Hi James
As I understand it, you want to move data from "AllData" to other worksheets based on the value of "AllData" column A. After the data is moved, should it be cleared, deleted or left on sheet "AllData"? If left on "AllData", will the other sheets be periodically updated from "AllData" or is it just a one time update?
John

[james598]

Thanks for your response! Yes that is exactly what i want to accomplish. I would like for data to be left on "all data" sheet and this will only be done one time.

[JBeaucaire]

Give this a try:

Please Login or Register  to view this content.

If you need the macro to "check" and make sure the sheets are there, we can add that so it could create all the sheets for you.

[james598]

WHOA!!! Yep that definitely did the job, and I am glad you added the post script at the bottom because it looks like my original macro didnt creat all the sheets, it stopped 3/4 of the way in.

[JBeaucaire]

This version will:

1) Create the sheets for you as it goes
2) Copy the data to appropriate sheets
3) Alphabetize the sheets
4) Give you an answer at the end to tell you if the rows copied match the rows of data

Please Login or Register  to view this content.

If you want, just delete all sheets except ALLDATA and run it.

[james598]

As soon as i run macro i get i get error message "subscript out of range"

[james598]

nevermind i forgot to rename the new sheet to "AllData" in new work book

So i run the macro and it does its thing and then i get the following error:

Run-time error '1004'

You typed an invalid name for a sheet or chart. Make sure that:

Name does not exceed 31 characthers


I just checked and some of the values in Column A are greater than 31 characters. I'll fix them and run it again. I dont anticipate having trouble but if i do ill let you know. Thank you soooooooooooo much for your help!!

James

[JBeaucaire]

We could make the macro name the sheets with the first 31 characters so you can use what you want in the column A values...

This is cool, I didn't actually know about the sheet name limitation...though it makes sense.

[james598]

Its ok it was only one record that was longer than 31 so i just changed it.....one last thing, is it possible for the macro to sort Column D on each of the newly created sheets Largest to Smallest???

[JBeaucaire]

Please Login or Register  to view this content.

=========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[james598]

wow you are a lifesaver!!!

Can i ask one more related yet different question? I need to include a "summary" sheet that will display the top rows (max of 15) of each subject. column E contains the variable that will be used to select these top values. I say max of 15 because some subjects have only one row, while others have 1000 rows. So for the subjects that have less than 15 rows, inlcude all 15, and for the rows that have more than 15 rows, include the top 15 rows based on column E (which is a number 1-4,000,000+).

Thanks!

[JBeaucaire]

Please Login or Register  to view this content.

[james598]

Incredible....thank you so much! I'll make sure to save this code so I can try and read through it to figure out what/how you did it.....closing thread now and give you perfect reputation feedback. I am sure I'll be back sometime in the future with a different question!

thanks,
James