Solved

Preserving macros when copying a speadsheet

Posted on 2011-02-22
12
526 Views
Last Modified: 2012-06-21
I often need to make a copy of an Excel workbook or copy a worksheet I have developed.  Since generally there are macros or code for the spreadsheet even though they copy across to the new workbook, the spreadsheet still refers to the old workbook.

Is there a way to ensure the worksheet refers to the code in its new location rather than the old?

mlmcc
0
Comment
Question by:mlmcc
[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
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34957892
> the spreadsheet still refers to the old workbook.

Do the macros (for example code attached to buttons) link to macros in the old workbook?

0
 
LVL 101

Author Comment

by:mlmcc
ID: 34958071
No, they refer to other sheets in the old workbook but the sheets exist in the current workbook.

The sheet I am building performs a summary of database information.  It refers to a sheet in the workbook to get the fields and search criteria.

mlmcc
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34958226
So perhaps the macros function correctly, but its the worksheet that's out of sorts?  How are you copying your worksheet?  Are you copying tabs to a new workbook, or is it a file copy?  A file copy should not have problems, especially if your filename is not being referenced directly in your macros.  So, I'm assuming you're copying tabs to the new workbook.

One way I've tried is to then close the old workbook.  With the new workbook, pull up the link manager (Excel Menu at the top left corner, then PREPARE, EDIT LINKS).  If you see links to your old file (which I suspect you will), then change the SOURCE to the current workbook (after saving the current workbook).  As you do that, the links to the old workbook will dissappear.

That may solve your problem.  Without further information, its hard to speculate what you are doing, but hope this helps!

Dave
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

Author Comment

by:mlmcc
ID: 34958265
I do it both ways.

It seems either way causes issues.

mlmcc
0
 
LVL 101

Author Comment

by:mlmcc
ID: 34958318
I don't see that menu
I am using Excel 2007

mlmcc
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 373 total points
ID: 34958324
> No, they refer to other sheets in the old workbook but the sheets exist in the current workbook

So it is inside the VBA?

I'm still unclear how you could refer to another workbook, so it woud help to see the code

One thing you can do to ensure that the code always points to the host rather than activeworkbook is to specify the ThisWorkbook
ie  
the first example below will work on the first sheet of the active workbook
the second example will work on the first sheet of the workbook hosting the code regardess of which book is active

Cheers

Dave
Sub Ex1()
Dim ws As Worksheet
Set ws = Sheets(1)
End Sub

Sub Ex2()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
End Sub

Open in new window

0
 
LVL 101

Author Comment

by:mlmcc
ID: 34958399
It seems to be in macros or code behind buttons.

I found one but it wasn't a button I was using.  I find no other links (found EDIT LINKS) to other workbooks.

mlmcc
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34959301
You said the new workbook still has references to the old one, so what is that reference, please be specific.  How do you know its referencing the other workbook?

Dave
0
 
LVL 14

Expert Comment

by:Don Thomson
ID: 34960810
Are the References in the MAcros to other sheets in the same excel speardsheets or to sheets in another file altogether.

If they are all in the same workbook - Just remove any reference to any drive letter and folders in the code. If you need to refer to another workbook - it either needs to be in the same folder or it has to stay in one location and never move

0
 
LVL 101

Author Comment

by:mlmcc
ID: 34962119
I don't need to reference other workbooks.

I am building and maintaining a P&L set of worksheets for a client.

I have VBA written that extracts data from his QuickBooks file and massages it into a summary statement showing sales and expenses.  
I make changes and add features in my copy of the workbook.  I have a copy of his current workbook.  I then copy the modified sheets into his workbook but some of the buttons remain tied to my workbook.  I know this because when I test the sheets I copied in, my development workbook gets opened and fields updated.

The code that did it the last time is on the start page and just refers to sheets by name but I noticed the button was still assigned to the macro in the old workbook.  When I reassigned it to the macro in its workbook, the problem went away.  There are no references to a drive or file name just the sheet names.

Since I have several sheets and many buttons for them to press, I was wondering how to ensure the buttons refer to the current workbook.  I know I can go through and reassign them all but I don't think that should be necessary.

mlmcc
0
 
LVL 101

Author Comment

by:mlmcc
ID: 34975670
In using this method

Sub Ex2()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
End Sub

Can I use the sheet name instead of the index?

mlmcc
0
 
LVL 101

Author Comment

by:mlmcc
ID: 34976717
I figured out a way to make it work.

Since many of the references are to the current sheet and the code is associated with the sheet, I used Me. rather than WorkSheetName. for thoase references.

I added a search for the referenced sheets to find the sheet index so I could reference them as

wb.Sheets(Index) where wb refers to this workbook

mlmcc
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

724 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