Solved

Preserving macros when copying a speadsheet

Posted on 2011-02-22
12
486 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
  • 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 100

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 41

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
 
LVL 100

Author Comment

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

It seems either way causes issues.

mlmcc
0
 
LVL 100

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 100

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 41

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 100

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 100

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 100

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

746 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

12 Experts available now in Live!

Get 1:1 Help Now