Adding calculation sheets from template file INTO any of the available sheet (using 2003 please)

Posted on 2011-03-01
Last Modified: 2012-06-27
This question acheives the reverse from the related question.

I have embedded a "template file - to export sheets" and want to copy and paste 2 sheets, "CalcA" & "CalcB", into any of the available excel files in the computer (this has already been achieve by Nutsch and sdwalker - by providing a dropdown list with the excel sheet which are opened at the time; I have attached their solutions as well).  

The trick, is that the formulas of these 2 sheets are "replaced" so that they point to the new workbook - otherwise the inserted sheets would still be pointed to the template file.

I have added the previous solution from Nutsch and Sdwalker, to show how the pop up was handle to show the excel files opened at the time.

Solution: One possible solution could be to change the workbook name in the template file FIRST, move it to the new workbook, and then close the template file, without saving - all done by the macro.  Just food for thought.

JP template-file---to-export-sheets.xls Sheet-with-real-data.xls nutsch-solution---import-data.xls sdwalker-solution---import-data.xlsm
Question by:easycapital
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 42

Expert Comment

ID: 35015147
sdwalker-solution has an invalid button in Sheet 1 calling a macro in a workbook you did not provide.

Ok - I pulled some structure together and you can see that it pulls the tabs over (one at a time, or multiple selection with ability to import more, etc, until done, even from other workbooks).

HOWEVER, I'm not clear on the links issue and I know this is at least half your concern.  If we move CalcA and CalcB over, there's still the problem that at least one of these tabs reference the DATA tab.  If we pull all the tabs over, then there is no link issue, as the links resolve automatically to the local workbook and if there were external references other than the source sheet, the would still need to remain intact.

So the only link management I have at this time, is when you moved the tabs over one at a time (instead of all at once) then you could actually go to the LINK MANAGER and ASSIGN LINKS to the current file from the source file.  Given that there could be other external links, I'm not comfortable going down this route unless you REALLY think it could be a timesaver, then happy to do some research on it...

Please let me know how this is helping and next steps/issues from your standpoint.


Author Comment

ID: 35047084

Looks really nice.  Will need more time to fully understand it.


Author Comment

ID: 35184784

I am looking at the file, and I must expressed incorrectly.  I am looking to export the information, not import it.  The reason that I need to do this is because I have templates files that contain the same tab names as those I want to bring the tabs into.  So, I have been thinking about a simple process that should work.  

I have drawn what I have in mind.  This is very useful for me.

Please advise.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 42

Expert Comment

ID: 35185338
This sounds like importing to me.  Even in your comment >>contain the same tab names as those I want to bring the tabs into

You also haven't provided any template file with 9 tabs, etc.  This appears to be wholly different from your original question.

Your original question:

>>I have embedded a "template file - to export sheets" and want to copy and paste 2 sheets, "CalcA" & "CalcB", into any of the available excel files in the computer (this has already been achieve by Nutsch and sdwalker - by providing a dropdown list with the excel sheet which are opened at the time; I have attached their solutions as well).  

This sounds and reads very much like what I've done for you.

So, its no surpised I'm a bit confused.

LVL 42

Expert Comment

ID: 35185465
ok - you attached several files.  Please do the following:

1.  Identify which one you're calling the "Template File" or upload one that has your 9 tabs plus 2 additional tabs (that would be 11 tabs)
2.  I assume then, you want to copy these two tabs (please identify the names of those tabs in your template) that will be copied to the destination file
3.  Please identify which of the files you uploaded is the destination file.  None of these have 9 tabs either.  Otherwise, upload a sample.  Also, include information on where the macro is supposed to "find the template file name/where the files came from to replace with blank" - is this a defined name, a link, or what??

to be clear, resolving links is not the issue, but having a well defined question appears to be and I appreciate you've created a nice diagram, but you've provided no supporting files that help make this real - at least in my mind.

My perception of the process you diagrammed:

1.  Prompt user for filename with data (the 9+2 tab file)
2.  Prompt user for output filename that also contains data (the 9 tab file)
3.  Somehow get the 2 tabs in Step 1 into the file in Step 2, and deal with any link issues.
Do I have this correct?

LVL 42

Expert Comment

ID: 35185611
Ok, sir.  I may have something you can test.  Perhaps the template and destination files don't have 9 tabs, but the CalcA and CalcB could possibly be the 2 tabs to move over, right?

Open all these workbooks (I can work this where they can be closed and you can select from the directory, but  let's work with this to ensure the process is correct)...

1.  Run the Button - macro in the TransferDataTabs r1 file - you will be prompted to select the file to obtain export data FROM
2.  Select from the pulldown what file to export FROM
3.  Select the tabs to EXPORT
4.  Select from the pulldown what file to export TO
5.  Hit the Transfer button.

If all this works correctly and per your process diagram, please let me know.  Sorry its taken me several gyrations through your comments to get here and crossing my fingers this is more aligned with what you need.

They say a picture is worth a thousand words - does the PICTURE below indicate what you would like to do?

Of course, at the end of the transfer, reset links to the destination sheet.  That's now automatic.


Author Comment

ID: 35203381
Hi Dave,

First, sorry for my delay in responding.  Second, thanks for the thorough follow up on this question - this will be very useful and used very often.  I do like the approach you took of building the macro on on ts own file, thereby making it very easy to apply it to any file.  Great!

Now - getting the subject of the matter.  I think that it would be easy to explain in action - meaning I will let you run it, and you will see what notification I get when I run the macro.  The only difference is that I added a tab called "Extra Tab" to template-file---to-export-sheets.xls and I want this tab to be imported into "Sheet with real data".  Once in there, I am interested in having the "Extra Tab" reside in "Sheet with real data" pointing to Only to "Sheet will real data" and not the file it came from (templat file to export).  I added one formula in the sheet in the "Tab Sheet" to make it simple to verify that it works.

I think if you were to just replace the file I am attaching and open it along with the other two files you provide  abovethen you will most likely see the notification provided when running the macro and will be able to solve it right then and there.

Best regards,
Juan template-file---to-export-sheets.xls
LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 35203929
Well, you were correct.  I saw the error and fixed it right there.


Author Closing Comment

ID: 35244551
Spot on!

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question