Solved

Moving a Worksheet to another Workbook

Posted on 2015-02-17
5
60 Views
Last Modified: 2016-02-11
I have two great Worksheets that work together in building out a  graphic.  I want to move them  to another Workbook where I plan to link the data in order to use the advanced graphic.  Here's my question; "how do I move or copy the WSs over to the other Workbook?"  I have about 15 Range Names that have to move over and one of the WSs has a Macro.  I've tried moving and copying to the other WB but it doesn't work when it is moved.

Any help would be appreciated.

B.
0
Comment
Question by:Bright01
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40615112
I find that Names (named ranges) move with the range they reference. If ranges are scoped to the worksheet rather than the workbook, they should move with the worksheet, so you could re-scope the ranges (if necessary) before moving the worksheet, and export then import the VBA module (or copy and paste) the macro across.

Are your 'Range Names' actually named ranges of cells or are they named constants?


Another approach would be to do a 'Save As' on the workbook that contains the ranges and macro, and then delete all unnecessary sheets.

i.e. instead of moving sheets from workbook A into workbook B, save workbook A as workbook C (so that the ranges and macro retain their context) and copy any worksheets that contain static data that you're linking to into workbook C.
0
 

Author Comment

by:Bright01
ID: 40615156
Interesting!  I didn't know what that "Scope" in the Range section had something to do with copying the Worksheet over.  In looking through the Ranges, they are named and the formulas are "=Offset....statements.  How can I change the Scope from "Workbook" to the Worksheet? Where or how do I edit it without having to re-write it?

Thank you,

B.
0
 

Author Comment

by:Bright01
ID: 40615163
After playing with it, not sure how to do it.  The formula references the other Worksheet.  So I'm not sure it could be associated simply with the Worksheet.......... Can I export them, then import them?

B.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40615166
See this link https://support.office.microsoft.com/en-gb/article/Define-and-use-names-in-formulas-2d9abce7-42cf-4a21-a6b4-f02786f690b7?CTT=1&CorrelationId=38d8f9b8-4ae6-49a0-8132-ef2c4a68e646&ui=en-US&rs=en-GB&ad=GB

Edit; our posts crossed. If names refer to the other worksheet in another workbook, you would have to edit them after combining the two workbooks, but it should be a simplification of the formula (i.e. simply trimming off the workbook path and filename so that the formula just refers to a sheet in the currrent workbook.
0
 

Author Closing Comment

by:Bright01
ID: 40616549
Simon,

Got it!  And it worked.  Thank you very much.

B.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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