?
Solved

Preserving macros when copying a speadsheet

Posted on 2011-02-22
12
Medium Priority
?
527 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
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.

 
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 1492 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 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