Thread: Writing variable source hyperlink macro (+255 char)

First of all: Thanks to all of you, who are way smarter than I, who've helped me accomplish years of MS Office reporting and tool building, without the need for classes, books, or yet another username and password!

It seems I've finally come up with a need that exceeds what I can piece together from recorded macros, and the solultions you've provided for others.

I have a rather large file that I use to unify the data from several different html based tools my company uses (that lack the tools I require), and several excel based reports that come from outside our company (that lack ALL the referred data I need to make them useful). It hasn't ever been a terribly easy tool to use, since I designed it for myself, and do not require the automation or protection that a less avid excel user would. It's recently become a priority item to incorporate in all our offices areas across the country, so automation is now a must. I've run into a wall on the last item.

At the front of the sheets I have a simple pivot table that aids in the quick and easy filtering of thousands of records. The point of this table is to be able to easily draw a route around a local area based on the most relevant jobsites. I've used a simple formula in the past to create the URL text to plot multiple adresses in google maps, so as to prevent back tracking, and create tighter routes. It's always there, I just paste the filtered data in, copy the resulting cell into a browser window and voila! It's starting to look like even that is too many steps for some. Problem is that after about 4-5 addresses I exceed the 255 character limit of =hyperlink, and a route can regularly include 20-30 addresses.

I've attached a file (STI Sample Data) with two sheets
(Sample Filtered Table) - A sample of the filtered table my pivot table produces which includes the addresses, separated into 4 columns.
(Sample URL) - Details of how the URL is built, with a sample of the single address hyperlink, and details of how additional addresses are added.

What I would like to do is create an empty sheet, into which one could copy the filtered table, and initiate a macro that would open their browser and input the URL based on address info it contains. OR EVEN BETTER, find a way to make that button automatically appear in the resulting new sheet created by double clicking pivot table values!

Sorry for the long post, it's a complicated request, and I figure solving a problem with too much data is easier than not enough. Thanks in advance, I know this isn't just a few lines of code.

OK, Ive got most of it down now. I found some code (already mostly suited to my applicatio) to identify the newest sheet after a double click in the pivot table,and rename it, and from there can automate inserting the form control to that sheet by Sheetname. I also figured out how to open and operate a browser window over the course of another project to automate logging a long series of web search results. But I'm still stuck on one part!!! The URL I need to build comes from 4 parts of a street address (with a "+" between each), and multiple different street addresses. The only way I have been able to do it is to create a string for each row and deposit it into a new cell, then reference each of those cells to create the URL. I want to temporarily store each of these strings without having to use sheet, and it needs to be a dynamic list so I can reference the 1st, 2nd and last string specifically since they require specific prefix/suffix, apart from the rest. Can anyone at least point me at a good article on loops or arrays that might detail what I'm trying to do?