Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Moving a Worksheet to another Workbook

Posted on 2015-02-17
5
Medium Priority
?
74 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
[X]
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
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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