Retrieve Row Data From Multiple Worksheets and Display in a hyperlinked list

[westond]

I have a rather large Workbook that contains some 120 worksheets. Currently each worksheet is a specific part and has serial numbers associated with a location. On a main sheet all specific parts are calculated across all worksheets based on their location. I now need to add a worksheet for each location and have a search that will populate these location sheets with the serial numbers of each diffrent part at the location, preferably hyperlinked back to the part's worksheet.

Please Login or Register  to view this content.

I Need a macro that will auto fill everything after the column headers and have part1,part2,part3 to be hyperlinked back to the appropriate worksheet.

Please Login or Register  to view this content.

Please keep in mind that I have over 100 parts with anywhere from 2 to 800 items in each part spread across 200 locations. I know this is more suited to a DB rather than a spreadsheet but its a bandaid until the company unrolls our ERP early next year. It's something that should have been done a long time ago.

Ive attempted to write my own macros, but VB is something that would take some time to learn and I dont have much time to complete this.

[rylo]

Hi

Can you convert your data description to an example workbook and attach to the post. Make sure it follows the structure of you real situation. Also add a sheet to show how you would expect your output to show.

1) Do you have a unique list of your locations, or does this have to be determined from your data. If it exists, where is this list.
2) Do you need to have the sheets created for the locations, or do they already exist.

rylo

[westond]

Here is an example file that has the format.

I do not have a master list yet. I only have the list of locatons from one area. I will have to make a ws for each unique location, however if I can get a macro that works on one then I can most likely extrapolate that to other location worksheets. The original does more to the data than this simple example, using some array (Ctrl,Shft,Enter) formulas and such... but writing VBA marcros envokes some real desk meets head banging from me.

[rylo]

Hi

Try this code. You will have to get rid of the extra blank items in your data. It does assume that you will have the output sheets created. Hopefully it will get you started.

Please Login or Register  to view this content.

rylo

[westond]

Wow that was very close. The script produced an out of range error and point to this line of code:

Please Login or Register  to view this content.

But, before it did It gave me this output on each sheet:

Loc1:

Please Login or Register  to view this content.

Loc2:

Please Login or Register  to view this content.

Loc3:

Please Login or Register  to view this content.

It looks like is breaking when it hits the s/n's for prt3. If we can fix that and polish the code just a bit. Can we set the output to start on a specific cell that way each time the script is run it will overwrite the previous output. Then have the script clear all the cells in the output sheet below its last position before going to the next location. Lastly, the names in my real sheet unfortunately are not as friendly as PrtX. However I can place a "P" as the begining character on eash worksheets name and then have the script get the part name from a specific cell on the part sheet. In the case of the example...A7.

I feel like Columbo, oh and one more thing..... can you recomend a good resource for learning the methods that excel uses?

Thanks again for all your help.

edit- I found out what was breaking the script. Apparently you cannot have any empty data validated cells at the end of a list for this script to work. I had a row of empty list cells at the end of the Prt2 workseet list and a row at the end of Prt3. Deleting the row in the Prt2 let the script continue and get all of the Prt3 parts listed then broke at the end of it. Kill that row in the Prt3 workseet let the script continue to its natural end.

BTW, how is it determining what Loc workseet to dump the output to. Just like my part names, my location names are not as nice as LocX. Can you point me to the line that matches the Location selection to the location worksheet?

[rylo]

Hi

1) on your edit, I did mention these extra items in my post. Sometimes these things aren't really all that clear, so I'm glad you found the problem items.

2) Resource: This is a question that gets asked a lot. Do a search and see what others have recommended. One of the most common responses is sites such as these. Have a look at the posts, and their solutions and try to work through them to see how they action. If you haven't found out about the debug items, then get them going. Makes things easier when you can step through the code, and using the immediate window, examine addresses, variables, what they have etc.

3) If you step through the code, you will see that I'm working through each item in column B. This has the name of the sheet that is the location. I then use

Please Login or Register  to view this content.

to get the sheet, then find the next output cell in column A, then set that range to the variable outpl. I use that as a reference point for the output. Again, if you get the debug going, you will be able to examine the variables and see what they have.

rylo