Preserving macros when copying a speadsheet

> the spreadsheet still refers to the old workbook.

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

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

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

I do it both ways.

It seems either way causes issues.

mlmcc

I don't see that menu
I am using Excel 2007

mlmcc

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

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

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

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

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

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