Solved

Moving a Worksheet to another Workbook

Posted on 2015-02-17
5
58 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:SimonAdept
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:
SimonAdept 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Loop within Select Case 3 26
onOpen 14 43
iSeries DB2 SQL - Request user input 12 7
Excel Formula Unlimited IF's 6 15
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now