Moving a Worksheet to another Workbook

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.
Bright01Asked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
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
 
SimonCommented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
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
 
Bright01Author Commented:
Simon,

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

B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.