Free Microsoft Excel 2013 Quick Reference

If cell changes update another file Results

Von Pookie's HTML Maker FAQ
Last updated: 07 July 2009

07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread:

PLEASE NOTE: This FAQ was written while we were using phpbb forum software. I have not checked these instructions for accuracy with the new vBulletin software we are currently using. It is possible that some items listed here may no longer apply.

Please note the following:
This is not the FAQ for the VBHTML Maker nor the YaBB Table Maker.These directions were written using Windows 2000 and XP, and Excel 2002 and 2003. I do not (currently) know the information for Macs or any other versions of Windows or Excel.

How can I show a small image of my worksheet in my post?
You can simply download the handy HTML Maker add-in! The HTML Maker is available for download at Colo's site. Currently unavailable

I have downloaded the .zip file. What is the next step?
You may need to download an extraction program such as WinZip to open the .zip file and extract the add-in file. For now, just extract the HtmlMaker.xla file to your desktop. Once you have this, you no longer need the .zip file.

Next, you need to locate the Addins folder on your computer. There are several places you may see a folder with this name. From the Excel help file: "Add-ins are stored by default in one of the following places:
The Library folder or one of its subfolders in the Microsoft OfficeOffice folder.The Documents and Settingsuser nameApplication DataMicrosoftAddIns folder."If you're uncertain which folder to use, open Excel, go to the Tools menu and select Add-ins. In the dialog that will display, click the Browse button to see the path for the AddIns folder it is using. Once you've located this, you can simply drag and drop the HtmlMaker.xla file into it.

Note: If you cannot find the Application Data folder, you probably just need to change a setting on your computer.Open Windows Explorer (or any folder) and select Folder Options from the Tools menu.On the View tab, look for "Hidden files and folders" under the Advanced SettingsMake sure the option "Show hidden files and folders" is selected, click OK, and look for that pesky Application Data folder again.

I have added the file to the AddIns folder, but I am unable to activate it in Excel!
Start Excel, go to the Tools menu and select Add-Ins. You should see a dialog box with a list of available add-ins, with a checkbox next to each item. Simply find the one labeled Htmlmaker, check the box next to it and click OK. You should now have a new menu between the Window and Help menus called HTML. You have now installed and activated the HTML Maker, and should be ready to go!

How do I use this add-in?
Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
[list=1][*]Make sure you have enabled HTML in your board profile.Click the "profile" link--which is in the group of text links at the top-right of every page of the board.In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.Click the "Submit" button to save any changes to your profile[*]Make sure the HTML Maker add-in is active (see "I've added the file to the AddIns folder, but I can't figure out how to activate it in Excel!").[*]Go to the HTML menu and select "Convert sheet to HTML"[*]Select the range you want to copy
Please select only the range (preferably with little to no formatting) that is crucial to the question you are asking.[*]Click OK[*]On the message box that pops up, click "yes" (I honestly have no clue what this does, but I always click yes and it works just fine)[*]Another message box will appear, letting you know how many characters the HTML Maker code will use, and how many characters that are remaining for you to use in typing the rest of your message. Click OK.[*]A new browser window will open, displaying what your copied cells will look like when posted to the board.[*]In this new window, click the "Copy code to clipboard" button.
If this does not work...[list=a][*]If you are running Windows XP with Service Pack 2 installed, make sure you allow popups on the page (they will be blocked by default) and try clicking the button again[*]As an alternative:In this same window click the button that is on the page that is labeled "View Source." This is located right next to the "Copy to Clipboard" button.Copy the entire code that is displayed there.[/list:o:4e41f3da2f][*]Come back to the board where you are entering your message, and put your cursor in the area where you type the body of your message.[*]Press Ctrl+V in the area where you type. If you don't know HTML, it will look like a lot of gibberish. That's normal, don't worry about it. You can still edit and add to your message if needed at this point. Do not edit/add to the code from the HTML Maker.[*]When you are finished, click the Submit button to post your message. The table should appear in your post just as it did on the browser page you copied the code from.
Do not preview a message containing HTML Maker code. You will not be able to use the buttons on the preview page to submit your message.Do not quote a post using HTML Maker code. You will receive errors (when quoting a message, simply edit out any extra HTML Maker code).[/list:o:4e41f3da2f]Back to top

Known Issues & Errors

I installed the HTML Maker add-in, but the "HTML" menu you say should appear on the menu bar is not there.
It is possible that you have installed the wrong add-in. This FAQ is for Colo's HTML Maker, NOT the VB HTML Maker or YaBB Table Maker. Making sure you have installed the correct add-in is the first step.

If you have confirmed that you installed Colo's HTML Maker correctly, make sure you have enabled the add-in. Go to ToolsAdd-Ins, click the checkbox for the HTML Maker and click OK. The menu should then appear.

If you have performed both of the previous steps and the menu is still not appearing, post your questions in the Questions About This Board forum.

I followed the directions, but after posting the page is not displaying correctly.
You can either edit the post yourself and remove ALL of the HTML code, or report the post by clicking the "Report" button () on the post to notify the moderators of the problem. A moderator will edit the post to remove any/all HTML Maker code so the thread will be able to display correctly.

As to why the page displayed strangely in the first place, it's usually one of two things: either you didn't follow the directions and copied the source of the page the HTML Maker generated instead of using the "view source" button on the page (yes, there is a difference and we can tell from the code if this is the case), or you have inadvertently posted incomplete HTML Maker code--which is much more common.

HTML requires both opening and closing tags; if you do not post the entire code, the closing tags are not included and the board cannot interpret the code correctly.

When using an HTML Maker example in your post make sure that you have copied all of the generated code and that it is pasted into the message in its entirety. There is also a character limit for each message (but to be completely honest, I have no idea what that limit actually *is*). If you try to use multiple examples within a single post, you can hit the limit quickly and the end of the code could be cutoff.

Error: "Could not load an object because it is not available on this machine"
You may be missing a file called ietimer.ocx, which is required. Click here to go to Colo's site and check if the file is already installed on your computer. This link will also automatically install the file for you if it is needed.
The file is also available for download from Microsoft: After downloading the file, it must be registered with regsvr32. The easiest way to do this is to right-click the file and select Install from the context menu.

Error: "Compile error." or "Can't find project or library..."
Open the Visual Basic Editor by pressing Alt+F11. Once in the editor, select the HTMLMaker in the Project window then go to the Tools menu and select References.
If there are any references that start with "MISSING," uncheck them and don't forget to save.
More information on this error can be found in this thread:

Error: "Error Number: 91, Object Variable or with Block variable not set..."
Click OK to close the dialog box with the error and go to Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

In Excel 2007, the directions are slightly different:
Click the Office Button and go to Excel Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings. Then you can Check the "Trust access to the VBA project object model" box and click OK.
(Thanks to Bradlee_S for posting this, because I would have never thought to add it otherwise)

Error: "Runtime error: 5 Invalid procedure call or argument."
When this error occurs, the following line will be highlighted in the debugger:
Open the Visual Basic Editor (Alt+F11) and go to Tools->Options. Select the General tab and make sure "Break on unhandled
errors" is selected for "Error Trapping."

First mention of this error (and solution) can be found in this thread:

I am unable to view formulas after clicking the formula link in the posted example
There are two or more posts in the thread with HTML Maker examples, which could cause a conflict in the Javascript code being used. There is no real "fix" for this issue that I know of. The only real workarounds are to not post a message using the HTML Maker code in a thread already using an example elsewhere, or if you absolutely need to show how the data is set up, use the HTML Maker to show your sheet and indicate what formulas are in what cells within your message. Users viewing the examples in Firefox may notice this problem as well. (I have this issue with Firefox no matter what, personally).

I am unable to copy the generated code to the clipboard.
As was discovered by board member Parry: "If you have installed Windows XP Service Pack 2, a Pop-Up blocker is installed [in Internet Explorer] as part of the pack and by default this will block active content.

When you select your cells and run HTML Maker it will create an HTML page of your image as expected, but the pop-up blocker will prevent you copying the code when you click the button to send Source to the clipboard. Simply click on the pop-up security information bar, then select Allow Blocked Content and you can now copy the HTML to the clipboard."

Back to top

[list][*]This add-in is simply to show a small "snapshot" of the selected area of your spreadsheet. However, some things will not display when posting an HTML Maker example, such as:Drawings and objects (i.e., items created from the Drawing toolbar)Clip art and other imagesCharts and graphsConditional formatting[/list:o:4e41f3da2f]Questions concerning the HTML Maker should be posted in the Questions About This Board forum.
If you have not used the HTML Maker add-in before, please post in the Test forum before making your actual post to make sure the example will display correctly. If any posts with HTML maker code end up "breaking" the page (including posts in the Testing forum), they will be edited by a moderator or administrator to remove the problem-causing HTML code from your post or the post itself will be deleted.

Find an error or something I missed?
Click to send me a private message

Is there any way to make the location of a link dynamic?

I have a cell linked from another spreadsheet, but every time I have a new
project the folder name changes.


I want the spreadsheet to update based on the location which is retrieved in
another cell in the work workbook. The path:


changes the file name will always remain the same.



First thing that could help is to turn off updating here are a couple of
routines to do that: Call Updates_Off at the top of your routine then call
Updates_On at the end

Public Sub Updates_Off()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub
Public Sub Updates_On()
With Application
.StatusBar = False
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.EnableCancelKey = xlInterrupt
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
End With
End Sub

I know that formatting takes a very long time to apply cell by cell if you
do not turn off screen updating.

Another way you can speed things up (if you are interrogating/populating
your sheet's cells row by row, col by col I.E. using .cells or .offset to
reference each cell)... Use Variant arrays to load your sheets cells then
process the array, changing the values you need to change then load that
array back to the sheet.

Dim vaData as Variant
Dim lRow as long, lCol as long
Dim addr As string

' Load your worksheets data
vaData = Range(ActiveSheet.UsedRange.Address).Value
addr = ActiveSheet.UsedRange.Address

' Process the data 1 row at a time, 1 col at a time
For lRow = 1 to UBound(vaData, 1)
For lCol = 1 to UBound(vaData, 2)
if vaData(lRow, lCol) = "X" then
vaData(lRow, lcol) = ""
'*** any other value changes to each row, col by col till
'*** the end of your data (you cant apply formatting here
'*** since your not directly referencing the cell)
Next lCol
Next lRow

' Put the array back to the same cells in the worksheet
ActiveWorksheet.Range(addr).value = vaData

Note that Variant data arrays must always be 2 dimensional for this to work.
The array defined is 1..X for each dimension based on the size of the block
of cells.

You can build a 2D array in code while parsing your XML file. then with one
statement load all that data to the block of cells. Just make sure it is
1..Rows and 1..Cols

hope that helps

"KevinGPO" wrote:

> I have programmed my own set of modules for Excel to read, parse a xml file
> then scan the Excel worksheets for "import/bookmark" tags and do a replace,
> pasting in the corresponding xml data. At the same time it does
> formatting/style/colors, etc. which is taken from the xml attributes &
> nodes.
> I found a 759kb xml file takes ages to parse. The excel template file is
> 376kb. Importing usually takes 9-10 minutes long. Is there any way I can
> optimise and speed things up (besides buying a faster PC - I have a P4
> 3.0GHz 512MB).
> Is there anyway to create/generate a valid excel file without having to load
> up Excel? What is this Office SDK and what can it do?


Is it possible to create a list in one workbook and then use another
workbook with a combo box to reference it? I know how to reference a
single cell, but for some reason, when i try to do the same for a combo
box, it says "Reference is invalid" or something along those lines.

The reason i want to do it this way is so that updating the list can
change the list across an entire server rather than having to update
the list on individual computers.....this also brings me to a second

When the list gets updated, the reference number change, so if i was
referencing Name!$A3 and then i decide to insert a new row so that A3
becomes A4, the value also changes so the new reference is to A4, is
there anyway to keep it so it references a certain range and does shift
in that can i just reference 1-500 and if something
shifts then they all shift and the new entry is included?

Thanks for any vews and thanks for any replies. I also apologize if the
thoughts are jumbled.


Akira5284's Profile:
View this thread:


I've come in on this discussion a bit late, but I had a similar - possibly
related - problem (when importing csv data with dd/mm/yyyy dates) a while
back, and found that it's a known problem with Excel 2003.

Microsoft Knowledgebase article 911750 - "The format of the dates is
incorrect when you use a VBA macro to convert a CSV text file in Excel
2003" - discusses a hotfix that's available to fix it, and also a code
modification which you can use.

I decided to use the code modification, rather than the hotfix, since it
(the hotfix) is a bit old and there have been some security updates since
it's issue and I couldn't be bothered messing around finding out what
updates I would need to reapply.

My knowledge on VBA and associated matters is extremely limited, but
assuming you're using XL2003 I suspect that what may be happening is that
when you open your csv file with VBA the dates which still appear OK are
actually imported as text, and then when you save them to a csv they show
incorrectly. Pure conjecture, I know, but it may be worthwhile using the
code modification suggested by MS when importing the data, and then see if
the correct format is maintained when you resave it.

Another article - "Converting date formats when the date isn't a date!" -
from, may throw some more
light on your problem.

Just a thought. Hope it helps. :-)



----- Original Message -----
From: "brawlsadford" >
Newsgroups: microsoft.public.excel.misc
Sent: Wednesday, May 16, 2007 4:41 PM
Subject: date format changes when I save to CSV via a macro

Hi Dave,

thanks for your response - I appreciate the help on this one!

Whether I open the new CSV file in Notepad or Excel, the entry reads:
9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note
the change in hour format too)

The same entry in the master CSV (in both Notepad and Excel) looks like
14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom -
dd/mm/yyyy hh:mm" )

When the data is pasted across into the macro workbook, the U.K. formatting
is preserved. After saving as CSV (automatically via the macro), the
formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I
then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm

It's only in the macro-saved version that this transposition to mm/dd/yyyy
h:mm is present.

Am I missing something?

Thanks Again,


"Dave Peterson" wrote:

> How did you verify that the dates changed?
> Did you reopen the CSV file in Excel or in Notepad?
> If you used excel, try using Notepad.
> brawlsadford wrote:
> >
> > I'm using a macro to extract rows of data from a large, master CSV
> > file -
> > breaking it down into chunks and re-saving it as smaller CSV files.
> >
> > One of the columns in the master CSV file contains date and time data in
> > the
> > format "dd/mm/yyyy hh:mm"
> >
> > The macro pastes this data into the workbook fine, but when the macro
> > saves
> > the sheet:
> > ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv",
> > FileFormat:=xlCSV, CreateBackup:=False
> >
> > ... all the dates have been transposed into mm/dd/yyyy!
> >
> > This doesn't happen when I save the sheet manually (Office button/Save
> > As...
> > CSV)
> >
> > My region settings are all set to U.K. - what's going on?
> >
> > Thanks, in advance, for your help,
> >
> > Saul

Cell format issue:
There are times when I run into an issue where
I am typing in formulas and somewhere along the way the format changes.

Cell B2 has data/number β€œ30”
Cell B3 has data/number β€œ50”
Cell B4 has data/number β€œ60”
Cell B5 has data/number β€œ70”
Cell B6 has data/number β€œ80”
Cell C2 has a formula β€œ=B2”
Cell C3 has a formula β€œ=B3”
Cell C4 has a formula β€œ=B4”
Cell C5 has a formula β€œ=B5”
Cell C6 has a formula β€œ=B6”

Cell C2 shows a result of β€œ30”
Cell C3 shows a result of β€œ50”
Cell C4 shows a result of β€œ60”
Cell C5 shows a result of β€œ=B5”
Cell C6 shows a result of β€œ=B6”

My first question is β€œWhat key board stroke am I doing to cause this
unwanted result?” (I must be bumping some key(s) to cause this issue because
every cell updated after the issue starts repeats the same format issue.)

My second question is, β€œHow do I change the cell to return numbers?”
1. I have tried Format Cells, select Numbers, 2 decimal places and the okay
button. Side Note: Despite selecting numbers in the catalog box... the Sample
box always shows the formula rather than the desired number. Why did the
format not change to numbers?)

2. I have also tried Tools/Options/ and under the window box verified the
formula box does NOT have a check mark.
(By the way I noticed that if I have selected cells without the format issue
and check off the formula box the return is the formula with a LEFT
alignment. Thus the cell cells are formatted as numbers where as the cells
with the format issue still is text. Why?

3. The only thing that seems to work is that I go to another file and copy
a cell that correctly returns numbers (The cell has to be one with a formula
created prior to when issue starts) and paste it in cells that returns
formula (with right alignment).
This is not a good solution at times and I have had to contact IT who can
not explain how to correct the issue so they reinstalling excel prior to when
the issue arises.

I want to link the fields on one worksheet to the values stored in several
other Excel files. (That part is easy.) But I want the ability to move the
underlying files to a new folder and be able to easily update all the links
in my worksheet.

One idea I had was to store the path in a specific field on the new
worksheet and use the value from that cell concatenated in as part of all the
link references. (Ex: store the path value in cell A1: [type UNC path
here]; then create a link that concatenates the value from A1 to the
filename, worksheet name, and cell reference info.) Then, if I move the
underlying files, I simply update the path value in my worksheet in the cell,
and all the link references automatically are updated. However, I have not
found the correct syntax for constructing a link reference using
concatenation. Will this idea work and, if so, what is the proper syntax, or
is there another better method for quickly updating multiple links when the
path of the source files is changed? Any help is greatly appreciated!!!
(P.S. The version is currently Excel97, but I suspect the solution will be
the same in later versions, as well.) Thanks!


I have developed a macro which imports data from another excel file.
Ex: consider I have 2 excel files A & B.
I run macro in file A today, it copies whatever data from file B.
say 20 rows have been filled in file A(starting from A5 to A25). I do some
changes on the file A which has few conditional formatting and forlumas.
I re-run the macro after 8 days in file A to get the new updated data from
file B.

The problem I am facing is >>>
"If I re-run the macro, it deletes the data from file A. It pastes
everything starting from top left cell.
When I generated macro in file A, I have selected top left cell while
recording macro.

My requirement::::::
Now I want the macro to put the data from the cell A27 onwards and also
retain the changes to the data from Cell A5 to A25.
So everytime I run the macro it should add data to the excel file from the
available blank cell and not copy over the same data.

Thnanking in anticipation

I am using Excel 2003 and have a master workbook that has many
references (links) to various other workbooks that are in Excel 4.0
format. These source files are created from a query each month.
Depending on the time of the year the source workbook may not exist
yet. I wrote a function (fileexists) in VBA that checks for the
exisatence of the source file. If it exists the function returns true
and then the I use the link to pull in the value to the master
workbook. If it doesn't exist I put a space in the cell instead. Here's
an example from a cell in the master workbook:


This all works great except I've noticed one problem. If the cell in my
master workbook was once updated because the source file existed, but
then I've renamed or deleted the source file, the value it originally
pulled in from the source file remains in the master workbook's cell.
The only way I can remove the previous value is to edit each cell's
function. When I do that Excel tries to update the link value and I
cancel that, then the cell is blank. Is there a way to have Excel
recalculate each cell and put a space in the cell if the source file
doesn't exist? When I open the master workbook I have it Update the
links then it tells me there are links that can't be updated (because
the source file doesn't exist). Regardless of either telling Excel to
continue or edit the links and not update them, the same thing happens.
The cells in the master workbook are not changed. I've also set Update
Remote References to yes and Save External Link Values to no on the
Calculations tab of Options. Can I do what I'm trying to do or is there
another way to accomplish this?


I want to link the fields on one worksheet to the values stored in several
other Excel files. (That part is easy.) But I want the ability to move the
underlying files to a new folder and be able to easily update all the links
in my worksheet.

One idea I had was to store the path in a specific field on the new
worksheet and use the value from that cell concatenated in as part of all the
link references. (Ex: store the path value in cell A1: [type UNC path
here]; then create a link that concatenates the value from A1 to the
filename, worksheet name, and cell reference info.) Then, if I move the
underlying files, I simply update the path value in my worksheet in the cell,
and all the link references automatically are updated. However, I have not
found the correct syntax for constructing a link reference using
concatenation. Will this idea work and, if so, what is the proper syntax, or
is there another better method for quickly updating multiple links when the
path of the source files is changed? Any help is greatly appreciated!!!
(P.S. The version is currently Excel97, but I suspect the solution will be
the same in later versions, as well.) Thanks!


i know your project is probably over by now but i came across your post
today and had to reply. i too suffer from "data slip" and you are the first
person to talk about. i am wondering if you have had any luck resolving the
problem. i understand select all needs to be done before sorting and i am
assuming you do this as well.

i have a similar spreadsheet in terms of size but mine deals with projects
and milestones so there are lots of dates on a single row. i began to find
dates that were way out of place. soon i began testing. i would enter a date
in a cell, save the ss, close, reopen, and the data was in a different cell.
with mine it didn't just slip to empty cells. i suspect your data copied over
other data also but it was harder to spot. my ss is shared by multiple users.
i have also noticed that the change history will not record changes correctly
(i.e. a cell which has not been changed will show a change made to it while
the cell whose data was changed does not show a change). the only way i can
resolve the problem is to unshare the ss, fix the cells and then reshare it.
this has really become a problem.

none of the techs i have spoken with have been able to recreate the problem.
the latest suggesting i have been given is to make sure all versions of excel
which edit the ss have been fully updated. like you i sort and filter the ss
all the time.

as a side note, i too am a novice compared to the average person answering
posts on this site but i had a few thoughts about your project if it could
still help. first, don't sort while the ss is filtered, this will hang your
computer forever - sort first then filter. second, this is a low tech
solution for comparing a ss without having a unique key column - what about
creating one ss which at least has all the last names filled in and make
another which has at least the zip and so on. compare each to another ss
which has been prepared the same way. when finished merge them back together.
i'm not sure it that makes any sense but maybe it will help.

anyway, anything you can share about "data slip" would be appreciated. thanks,


"ConfusedNovice" wrote:

> Thanks, Dave. This thread is getting unwieldy, but I
> *think* I understand what you're advising.
> Sticking with the issues at hand, yes, the records are all
> one per row. Last Name is probably the best key, but there
> are some records with only a Company Name, & a few with
> only an email name. I have been using Last Name & Zip
> Codes to try & match things up.
> I discovered that when sorting some of the intermediate db
> versions by "new" entries, those newest entries were
> mostly correct. I am thinking of isolating these records &
> copy/pasting them on to the oldest db version. The idea is
> to try for a cleaner sheet to compare to the current one.
> Maybe it would be better not to merge these? It's crunch
> time - I will try your formulae on a test file today.
> And moving ahead: If I can execute the comparisons you
> suggest, and if I can achieve a cleaner list, then what's
> the best way to prevent data from slipping into the empty
> cells in the future? Should I make a new index column,
> number each record, and append & number all new records?
> Can people safely sort & filter the data? What if they
> update a record in a filtered list & then re-sort? Or
> Find/Replace & then re-sort? I still don't understand how
> some of the data slipped out of one record & into another.
> Until I do understand, I'm afraid it will continue to
> happen & all this work will be for nothing.
> Thanks again! It's an ugly way to learn Excel.
> Jane
> Subject: How to sort/update large excel db
> From: "Dave Peterson" >
> Sent: 9/30/2004 1:05:46 PM
> Just to clarify, is your data laid out one record per
> row? You don't use
> multiple rows to represent one "logical" record, do you?
> If you do, then this will make it even more difficult.
> But to match/merge two different worksheets, I would think
> that you'd need to
> have a unique key/index into the data.
> If you don't have this key, how do you match up your data
> to look for
> differences?
> I wouldn't put too much faith in having that unique key in
> the last name
> column--but maybe you can use a couple of columns that
> could serve as that key.
> For instance, if you had the lastname, firstname, address
> in 3 separate columns,
> you could use a helper cell and do something like:
> =A2&char(10)&b2&char(10)&c2
> (The char(10) probably won't appear in your data and will
> serve as a field
> delimiter--just in case there's records that might look
> identical when
> combined.)
> But even if you did this, you'd have to make sure that
> these fields weren't
> changed. If any were changed, then you couldn't use them
> for a match between
> worksheets.
> To find out if the records in one worksheet are in the
> other (based on any
> single column you can use--in your data or derived from
> your data):
> =isnumber(match(a2,sheet1!a:a,0))
> and drag down.
> You'll see True if it appears on that other worksheet.
> And use the equivalent to check the other worksheet.
> Once you've found your Falses--you'll want to inspect them
> to see if they should
> be cleansed--cleaned up to make them match.
> You could end up fixing the data on either worksheet--just
> to find the matches.
> Once you get rid of all the Falses, you can do the =vlookup
> () stuff to match
> merge.
> I'm not sure if all this work can be done on a quick
> schedule. This kind of
> stuff usually means fix, check, fix, check....until you
> can't find any more
> differences. Then you let someone else review it and it
> starts again.
> Good luck,
> ConfusedNovice wrote:
> >
> > Still trying to understand what I've got. There is no
> > column with all unique entries. Every column has blank
> > cells someplace. On 2 of the earlier database versions,
> > the leftmost column (Column A) is set up as "Index
> > Column," but not all the records have an "Index" number
> in
> > this column - some of them are blank, & some have zeros
> in
> > Column A. The most recent master does not have Index
> > column, it starts with a Last Name field.
> >
> > This brings me back to the question of how to prevent
> > names/values from slipping into the blank cells when
> > sorting & updating records. I have been reading a lot of
> > Excel info, but this is *not* my area at all, & I want to
> > fix these problems, not compound them. Should I be able
> to
> > identify key column(s)? If yes, how? And if no, is there
> > any safe way to sort when blanks exist in every column?
> > (Or can it be a Key & contain multiple blank cells?)
> >
> > There are ~17,000 records, & when the wrong values move
> > into a blank cell, that record doesn't indicate that it
> > has been changed. (Probably because it was not opened?)
> >
> > I'm seriously running out of time. I've got a pretty big
> > list of changes ready to enter directly on db records. I
> > also have isolated a few hundred recent entries by date,
> > verified them, & placed them in a separate worksheet.
> > (Hoping to append to a clean main list & sort into
> place.)
> > I don't think it's possible to verify every row.
> >
> > Yes, no matter how you look at this, it's a pain! I'm not
> > really dumb, just a total rookie, & your help is
> > invaluable.
> > To summarize:
> > Whatsup with "Index Column"?
> > How to identify/designate Key columns?
> > Implications of having blank cells throughout sheet?
> > Fastest way to add/edit records if I go back to an older,
> > cleaner version of the database?
> >
> > Thanks again!
> >
> > Jane
> >
> > >-----Original Message-----
> > >I'm saying that if you have two "live" copies of the
> > workbook, it'll be a pain
> > >to merge one of them back into the other.
> > >
> > >There's nothing built into excel that will force you to
> > make sure your key
> > >column entries are unique.
> > >
> > >When I do this, I spend some time verifying that the
> data
> > is at least
> > >consistent--no additional entries with the same key were
> > added. If you can
> > >believe/verify this, then, yeah, you can do =vlookup()'s
> > to merge the updated
> > >values into the one real workbook.
> > >
> > >It's just in my experience, avoiding this is usually
> > better. (Just give the
> > >file to someone else and tell them to return it when
> > they're done--and put a
> > >freeze on any other changes.)
> > >
> > >

I am trying to create a formula that allows me to update a link to another
workbook by changing the value within a cell embedded in the current
workbook. I was thinking that InDirect would allow me to do this, but I
cannot get it to function properly. As an example, I am trying to update the
worksheet reference that is pulling data into the worksheet:

HyperionShared2006P2"file name""worksheet""cell reference"

What I need to do is keep the workbook reference but change the "P2" as our
periods change, ie to P3, P4, etc, without have to do an edit replace. I was
hoping to place a cell in the worksheet that would represent the current
period, and when this cell is updated it would also update the "P" reference
in the link referenced above. This will also be necessary for the
"worksheet" reference in the example above. If anyone has thoughts on how to
accomplish this it would be appreciated. Thanks.

I have a problem getting another XLS file to open from a hyperlink within a
XLS file.

I have a workbook containing several worksheets of business processes. Each
of these worksheets contain hyperlinks to the same related external documents
(doc, xls, ppt etc), i.e. single file linked to from multiple places. Each of
these external documents is distributed around our network and are subject to
being moved about. As an external document may be referenced from upward of
20 locations within the XLS, any changes to the target document causes a
large maintenence effort. So I included an index worksheet which contains 3
columns. One for the hyperlink display text, one for the hyperlink path and
one containing the hyperlink formula. I can now copy the 3rd cell to the rest
of the worksheets where required. The hyperlink path points to a single
(fully quilified) directory containing shortcut files (.lnk) to the external

On worksheet "Index"
A1 TextToShow
B1 ServernameSharenameshortcutfilename.lnk
C1 =HYPERLINK(Index!$A$1,Index!$B$1)

Thus I can amend the shortcut file to point at a new location should the
target file be moved or renamed etc. I can also I update the path and/or text
on the index worksheet and all the other hyperlinks in the workbook are
updated automatically.

This has worked fine for over a year, and still does for all but XLS files.
Word DOC, powerpoint PPTand even PDF load up with no problem, but an XLS just
results in turning the mouse pointer to a "timer" but still allowing the
normal "arrow" functionality. The hyperlinked XLS file never displays.

If the hyperlink path is pasted into the Start>Run dialogue, then the file
is loaded aok!

This used to work fine for XLS files. Unfortunatley, the last time that I
can verify that ths worked without the XLS issue, is back in October 2005. I
suspect a security settings update has caused this but can't find anything.

Other points:
This problem manifests itself on all our machines.
The standard warning message about possible virus infection from this file
type is displayed (only occurs if EXCELL has located the target file)
Windows Task Manger Processes tab shows that EXCEL memory increases to hold
the file
Windows Task Manger Applications tab does NOT show the called XLS file
PCs are running WinXP Pro, Office 2003, both with latest updates as of

Anyone got any ideas.

Thanks in advance


I am using a part description and pricing list from one file and using that information in a separate quoting file. What I am trying to figure out is when I'm in the quoting file and change the part description what can I do to make the price update accordingly with the part description.

I.e. If it part description listed in the quote file says, "Part A" with a price of "$1.00". I want to be able to go in an change to "Part B" and allow the program to find the corrolating price of "$0.50" without me having to go in by hand and change it. If any would need more I can attach an example.



I am looking for assistance in creating and formatting several vba macros in a large workbook. Specifically, I need the following:

1. On initialize, run a macro I have already drafted which hides and unhides certain rows based on the presence of data. This only needs to be run one time at the start. Then, another macro takes care of hiding and unhiding rows during use.

2. On initialize, run a macro which checks to see if macros are enabled and if not, unhides a sheet instructing the user to enable macros and how to permanently enable macros/trust the sheet.

3. Create a macro which is optimized to read a specific section of a sheet to see if a value has changed, and if so, hide or unhide the row based on the value in that row/cell. This sheet is linked via expressions to the value of an Input Sheet. Either sheet can be used to drive the hide/unhide function. I have created a macro which does this, but it takes far too long as it goes through 3000 rows each time and what I want, I think, is to optimize it and create several smaller sections which will check to see if anything in that group of rows has changed and then, if so, go through and check the rows in that section alone to hide and unhide only if a cell in that range has changed. I don’t really care how this is implemented, it just must be fast and efficient and not cause the sheet to spend 2-3 seconds auto-calculating each time a value changes.

4. Design a macro (push button) which will copy values from a bunch of cells in one workbook and then paste them in another specified workbook. Basically, I want to be able to update different calculations in a new workbook on hidden sheets and be able to copy and paste input values from one older workbook to a new workbook with the new calculations in the background. For ease of future use, I would want these pasted to a hidden sheet which would then populate the input page until they were overwritten by the user. That way, I could change the format of the workbook in the future and retain the ability to copy and paste without screwing up the formatting. I was thinking you could use a formula in the input cells like: if(cells on hidden page = “”, “”, cell value of hidden page) Then, the user could overwrite the formula with new values if they chose.

Obviously, there may be better, more efficient methods to doing this, which is why I am asking for help. This is going into a fairly sophisticated workbook with 30 plus sheets, but I am only really dealing with 2 main sheets to implement this, plus the hidden sheet as mentioned in 4. Also, this workbook will be sold commercially, so I cant freely distribute it to everyone to take a look at. I have a paired down workbook with just the 2 sheets I am referencing which you may look at before you take the job. Then, I can interface with you directly regarding the full sheet, if necessary.

Please let me know if you are interested so we can make sure we are on the same page before you invest too much time into this. Thanks in advance for the help.

Here is a link where you can download the limited file:


I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.

My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.

What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.

Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.

What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.

Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file.

    Dim i As Integer 
    Dim Source As Workbook 
    Dim Destination As Workbook 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.EnableEvents = False 
    On Error Resume Next 
    Set Destination = ThisWorkbook 
    With Application.FileSearch 
         'Change path to suit
        .LookIn = "C:Documents and SettingsnochhauDesktopExcelprosjekt" 
        .FileType = msoFileTypeExcelWorkbooks 
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To .FoundFiles.Count 'Loop through all
                 'Set Paste target first
                With Destination.Sheets(1) 
                     '''''' Here I would then have to make an if to do a different selection based on what input file I have
                    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 
                End With 
                 'Open Souce file
                Set Source = Workbooks.Open(.FoundFiles(i)) 
                 'Copy and paste to destination
                Source.Sheets(i).Range("A1").Copy Tgt 
                 'Close source file without saving
                Source.Close False 
            Next i 
        End If 
    End With 
    On Error Goto 0 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.EnableEvents = True 
    Application.CutCopyMode = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if anyone can help me I would be very grateful!

Hello everyone!

(Posted as well in

I am trying to make a master workbook that will update itself from offline files that are in a specified working folder. I am using this code to grab the file names:

     'create variables
    Dim j As Integer 
    Dim i As Long 
     'clear the contents of the old search.  This is so old results don't "ghost" into this search
     'this is the search function
    With Application.FileSearch 
        .LookIn = "C:data" 'this is where it looks. Modify this to constrict the search area
        .FileType = msoFileTypeExcelWorkbooks 
        .SearchSubFolders = True 'change this to False to not look in sub-directories
        j = 1 
        For i = 1 To .FoundFiles.Count 
            ActiveWorkbook.Worksheets("Files").Cells(j, 1) = .FoundFiles(i) 
            j = j + 1 
        Next i 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code does the following: it look for all XLS files in the Data folder and lists them in Files.

However, I am stuck on the next part. I cannot find a way to use this retrieved data to link information from the offline files. If I use ='path[file.xls]worksheet'!range and replace it with cell names (A1, A2, etc) I get a #REF! error.

Also, I have been searching this site and several answered topics suggested INDIRECT. Nevertheless, the listed files are going to remain closed, so another command is needed.

Is there a way to do this? I don't mind if its VB code or a formula. Any input will be greatly appreciated.

Thank you in advance,

I would like to import data from another workbook, and I have written some code to do so (see below), which runs OK but unfortunately causes subsequent problems (see: lother thread) This is why I'd like to substitute it. For me the difficult part comes with handling the images and avoiding worksheet-naming-collisions

What is the best way to do the following: I'll try some pseudocode here:
For each worksheet in sourcebook do
- import/copy a defined range to a new worksheet in targetworkbook
- import/ copy images (located in a single chart) to the worksheet (NOT chart) in tagetworkbook
- avoid worksheet-naming-collisions: (all sheets always go by the format: "some letter(s), number(s)" or "XXX##") Names in targetbook have priority and may not be changed; freshly imported sheet-names should keep their number but change their letters.

Example: In the targetbook worksheets are called "C5", "C3", "C4"; and in sourcebook they are called "C1", "C2", "C3". After the import the targetbook should hold "C5", "C3", "C4" (unchanged targetbook worksheets) and "D1", "D2", "D3" (modified sourcebook worksheet: here "C" was changed to "D"), while the latter ones hold some imported data (range+images) from their sourcesheets.

     'This macro imports worksheets from other sourceworkbooks.
    Dim wkbSource, wkbTarget As Workbook 
    Dim wksSource, wksTarget, ws, wt, w, finalPosition As Worksheet 
    Dim myrange As Range 
    Dim numCollision, newWorksheetsNum, oldWorksheetsNum, i, j, lowestRow, chartNo As Integer 
    Dim numInsufficient As Integer 
    Dim text, myTag, headerText, defaultInput, changeName, nameNow As String 
    Dim choA As ChartObject 
    Dim shpImChart As Shape 
    Dim sFileName As String 
     'no screen repainting: makes macro run faster
    Application.ScreenUpdating = screenUpdatesBegin 
     ' set the active sheet as the targetMap
    Set wksTarget = ActiveSheet 
    Set wkbTarget = ActiveWorkbook 
     'show message:
    headerText = "Importing experiments: Quick Guide Line" 
    text = "With the next dialog you will choose the source-file you wish " & _ 
    "to import. Please make sure those data are in the right format " & _ 
    "as the import may fail due to any unexpected incoherence in data." & _ 
    "Avoid naming conflicts by using a tag for your source-data. Furthermore " & _ 
    "do NOT import any cell twice, not even under different tags!" & _ 
    "Please note: Importing may take quite a while!" 
    MsgBox text, title:=headerText 
     'Show the open dialog and pass the selected file name to the String variable "sFileName"
    sFileName = Application.GetOpenFilename 
    If sFileName = "False" Then Exit Sub 
    Workbooks.Open Filename:=sFileName 
    Set wkbSource = ActiveWorkbook 
    Set wksSource = ActiveSheet 
     ' how many new sheets are there
    newWorksheetsNum = wkbSource.Worksheets.Count 
    oldWorksheetsNum = wkbTarget.Worksheets.Count 
     'Estimate the amount of naming collisions possible between old target workbook and
     ' the source workbook: very slow, needs to be improved by a list
    numCollision = 0 
    numInsufficient = 0 
     'runnning through all source-sheets
    For i = 1 To newWorksheetsNum 
         'counting insufficient sheets
        If Not sufficientWorksheet(wkbSource.Worksheets(i), minSNRatio, bSNImportFilter) Then 
            numInsufficient = numInsufficient + 1 
        End If 
         'checking for name collisions
        For j = 1 To oldWorksheetsNum 
            If (wkbSource.Worksheets(i).name = wkbTarget.Worksheets(j).name) Then 
                numCollision = numCollision + 1 
                Exit For 
            End If 
        Next j 
    Next i 
    If oldWorksheetsNum  1 Then 
         'Second User-input: Which tag shall be added to each name of imported worksheets in order to avoid naming
        If (numCollision = 0 And numInsufficient = 0) Then 
            headerText = "Import: Ready for Import!" 
            defaultInput = vbNullString 
            headerText = numCollision & " name-collisions! " & numInsufficient & " Insufficient sheets" 
            defaultInput = "00" 
        End If 
        text = "Which tag would you like to add to each name of an imported worksheet?" 
        myTag = Application.InputBox(Prompt:=text, title:=headerText, Default:=defaultInput, Type:=2) 'Type:=2 means
"String-input expected"
         'change name of origin-worksheet, so no name-collisions take place = "C" & 9998 
         'set default tag to nothing
        myTag = vbNullString 
    End If 
    For i = 1 To newWorksheetsNum 
        If sufficientWorksheet(wkbSource.Sheets(i), minSNRatio, bSNImportFilter) Then 
            nameNow = wkbSource.Sheets(i).name 
            If (nameNow  "results" And nameNow  "cc" And nameNow  "hn" And nameNow  "brief") Then 
                 'make a new almost blank sheet
                Call addAnotherAlmostBlankSheet 
                Set finalPosition = ActiveSheet 
                 'name source Chart
                If Sheets(i).ChartObjects.Count > 0 Then 
                    Set choA = Sheets(i).ChartObjects(Sheets(i).ChartObjects.Count) 
                End If 
                 'copy almost everything:
                lowestRow = Cells(65536, 1).End(xlUp).row 'lowest USED data row with values in
                If copyImgAtImport = True Then 
                    wkbSource.Sheets(i).Range("A1:O" + CStr(lowestRow)).Copy Destination:=finalPosition.Range("A1:O" +
                    finalPosition.Range("A1:O" + CStr(lowestRow)) = wkbSource.Sheets(i).Range("A1:O" + CStr(lowestRow)).Value

                    Call makeUpFormatedSheet(ActiveSheet) 
                End If 
                With finalPosition 
                     'check if Cell is active or inactive
                    If .Cells(3, 1).Value = "inactive" Then 
                        Call activateCellSheet(False, showFrapHelp) ' this inactivates the cell (unusual)
                        Call activateCellSheet(True, showFrapHelp) 'this activates the cell (normal)
                    End If 
                    Call updateSheet ' update Sheet
                    .name = wkbSource.Sheets(i).name & myTag 'rename Sheet
                End With 
            End If 
        End If 
    Next i 
     ' close the source-file WITHOUT saving any possible changes
    wkbSource.Close SaveChanges:=False 
     ' screen repainting set up again:
    Application.ScreenUpdating = screenUpdatesEnd 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Dear Experts ,

For this project ,i will do Paypal Credit -Only after successful completion of the project .FYI -The project is being presently carried out by myself using selenium and i thought to see how it happens using Macro .Friends say that it is easly possible using DOM method but i seriously have no clue about it

I will prefer only senior pros to attempt this project who have a free open mind to work with me with a little time and patience because sometimes we encounter obstacles we don't foresee at the outright start of the project is not a concern for me ,only perfect results are project lined up will be a higher project of similar nature valued at 100$ for which i intend to work with the same expert senior pro who volunteers for this project !

For this project site ,let me give a brief idea about what i intend to do ....we have a customer who has prices for items tendered online through his website .(Please note that while logging in the site ssl certificate is expired so it throws a security exception error and i want macro to handle this ,manually i need to select "Allow this exception "before proceeding ..i will appreciate any solution needed to fix this in the automated solution

.........basically it is a e-tendering system with customer floating various e-tenders through this site ...the intention of project is replacing manual filling of any tender by connecting EXCEL to website using macro( DOM method )or any other method ...(.net connection will be available )...if macro cannot connect to net ,then a msg box should appear that "Connection to net not available ,please connect to internet and retry

Manually ,i need to login ,select the tender from the tender list ...then press the radio button and press fill for filling the tender ...Any tender filling involves selecting and filling ...once we select and then proceed to fill ,filling itself is comprising of 2 stages ,technocommercial bid and price bid

Say for example -Tender ABC,XYZ,EFG with two section's

->Techno commercial bid divided in 4 parts

a.)GTP parameter - (Basically radio buttons are there ,they need to be selected and corresponding dropbox need to be set to yes indicating that we wanna quote for that item

b.)Document upload -Here in excel file i will give path ,i want macro to go to that path and select file and upload in the corresponding slot only (to make thinsg easy we will track the uploading by position ,i.e position 1 ,position 2 ,etc

c.)Delivery - standard value need to be filled for all tenders in just 2 slots (very easy ,always 2 slots and same value to be fed in this slot boxes we will identify by position and will always fill 0 here )

d.)Confirmation -Standard value need to be filled for all tenders in just 2 slots (very easy ,always 2 slots ,we will record them by position and fill value as per embed sheet sheet 1 user fed data ..mostly will always be constant )

Now once we get done filling the price bid ,we need to fill the price bid for the items for which we have filled the technical bid ,

II.)Price Bid -For the items in which we have set GTP parameters to "yes" we need to fill prices

In the price bid ,always observing a strict pattern ,item description and item codes (varying from tender to tender )...with standard pattern will appear in list .

depending on tender selected in attached input sheet ,macro will select the tender and press fill ....then for item codes as per cell in attached sheet wil record value and put them in strict positions (the positions of the prices wont change )Once done for all positions as per attached sheet ,the last position FDD price gets computed by function embedded in site itself and macro needs to press a button "Compute FDD "...once done page refreshes and the fdd price appears ...macro then has to press save button ......

Note that we will be able to fill price bid for only those items to which we have ticked yes in the GTP paramter in technical bid ...for rest wherein we have selected no there ,we have to equally select "Not quoted here "and press save button ...for a msg box which pops up "are you sure that you dont want to quote ,yes needs to be selected )..dunno whether macro can be automated to do this ....with selenium we are trying to do this ......

Now See attached input sheet .i fill it with all needed data required to fill any selected tender

Macro will take login id and password,tender code from excel sheet embed sheet 1 (using this login needs to happen tender needed to be selected for filling and prices required for filling the tender

In embed sheet 2 (for filling GTP of technical bid),i give inputs for filling the GTP ...using this macro will select the radio button for the item codes and based o excel sheet select yes or no accordingly for the selected item codes

In embed sheet 3 (for filling GTP of additional documents required ),i create embed sheet 3 ,wherein i will give file paths ...macro will peruse file paths and attach the file ...if it doesnt find a file path will give a box ,the fil could not be found another path .......

In Embed sheet 4 (for filling delivery of technical Bid) the 2 slot boxes always 0&0 has to be fed

In embed sheet 5 (for confirmation of technical bid ),8402.90 and 10.3 will need to be filled from embed sheet 1 value
1 needed to fill the tender -user id ,login ,tender code to be selected and a macro button there so that as soon as i initiate the macro button ,the website opens

Then based on user fed data through excel sheet data ,connection is established and data gets entered in respective fields as per user fed data in excel sheet ,

I need interested bidder to mail me his/her email id ...the attachments are huge and i cant attach them here ,

Solution approach - I want the code to be commented on every line so that i can understand the solution rather than just have it ,

Second i want screen updating to be turned on so that i can see real time the execution ,i don't mind effeciency reduction through this ,i would rather prefer seeing this happen ,

Third ,please see the input file carefully ,i have written how the macro needs to operate and have tried to explain in very easy language ,but i yet think this will need a further bit of explaining,for its a bit complicated project ,

Would love someone to dare to attempt it cause firstly i dunno whethe rit is possible or not using excel automation(people say DOM can be used to do this so i post it here ) but surely know its possble using selenium ,

Let' see ,i only want expert's to attempt this please ,i will select only the people i feel can actually attempt to solve this ,for people who did my earlier projects ,my obvious preferences but then i won't mind senior pros attempting my projects ,

Humble regards ,

Amlan Dutta

I have a report that I have picked up from another user and I'm trying to avoid doing all the long-winded bits that take forever, like updating charts. The one bit I am having trouble with is that the source data from the charts comes from a different external source each week. I have come up with the following code, which works fine if the workbook with the current source data is already open, but comes up with Run-time error '1004' if it is not.

I am assuming the cause is that the chart wants to update after each change so is trying to update with reference to an unavailable source. I am already using a workaround in that I first use the macro to update the graph with the data it is currently using (thereby opening the workbook with the current source data), but at some point I am going to have to hand these tasks on and it may not always be clear where the graphs are getting the source data.

Anyway, I am hoping someone could help with one of two solutions:

1 - If I can find a way of getting the path and filename the chart is using I could use that to open the current source data
2 - If there is a way of telling it to update all the series at the same time, thereby not having it refer to an unavailable source

My first solution was to just copy and paste the data from the source file but I have been tasked to find another way!

Sub UpdateFPNCharts() 
    Dim ReportFile As String, FPNChartSource As String 
    Dim Col As Integer, SlashPos As Integer 
     ' Return the name of the open workbook as a string variable
    ReportFile = ActiveWorkbook.Name 
     ' Get the new file to open
    FPNChartSource = Application.GetOpenFilename(, , "Select FPN file to use...") 
    If FPNChartSource = "False" Then Exit Sub 
    Workbooks.Open (FPNChartSource) 
     ' Return the column number of the last column in the source data
    Col = Cells(1, 1).End(xlToRight).Column 
     ' Select the original file
     ' Get the filename and add an open square bracket for use in naming the source _
    For the chart data 
        SlashPos = InStrRev(FPNChartSource, "", Len(FPNChartSource), vbTextCompare) 
        FPNChartSource = Mid(FPNChartSource, 1, SlashPos) & "[" & Mid(FPNChartSource, SlashPos + 1, Len(FPNChartSource)) 
        ActiveSheet.ChartObjects("Chart 259").Activate 
        ActiveChart.SeriesCollection(1).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(1).Values = _ 
        "='" & FPNChartSource & "]FPN'!R23C96:R23C" & Col 
        ActiveChart.SeriesCollection(1).Name = _ 
        "='" & FPNChartSource & "]FPN'!R23C1" 
        ActiveChart.SeriesCollection(2).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(2).Values = _ 
        "='" & FPNChartSource & "]FPN'!R16C96:R16C" & Col 
        ActiveChart.SeriesCollection(2).Name = _ 
        "='" & FPNChartSource & "]FPN'!R16C1" 
        ActiveChart.SeriesCollection(3).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(3).Values = _ 
        "='" & FPNChartSource & "]FPN'!R18C96:R18C" & Col 
        ActiveChart.SeriesCollection(3).Name = _ 
        "='" & FPNChartSource & "]FPN'!R18C1" 
        ActiveChart.SeriesCollection(4).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(4).Values = _ 
        "='" & FPNChartSource & "]FPN'!R20C96:R20C" & Col 
        ActiveChart.SeriesCollection(4).Name = _ 
        "='" & FPNChartSource & "]FPN'!R20C1" 
        ActiveSheet.ChartObjects("Chart 260").Activate 
        ActiveChart.SeriesCollection(1).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(1).Values = _ 
        "='" & FPNChartSource & "]FPN'!R9C96:R9C" & Col 
        ActiveChart.SeriesCollection(1).Name = _ 
        "='" & FPNChartSource & "]FPN'!R9C1" 
        ActiveChart.SeriesCollection(2).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(2).Values = _ 
        "='" & FPNChartSource & "]FPN'!R13C96:R13C" & Col 
        ActiveChart.SeriesCollection(2).Name = _ 
        "='" & FPNChartSource & "]FPN'!R13C1" 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

No luck finding an answer? You could always try Google.