Copying a spreadsheet with macros

[WayneB]

Hi: I have a spreadsheet with macros that I'd like to copy to new
spreadsheets. But when I do the macros open the old file. I've tried
moving the spreadsheet to the new spreadsheets but then it says the macros
don't exist. any ideas? wayne

 

[David McRitchie]

Hi Wayne,
How are you invoking the macros?
Alt+F8
Toolbar buttons
Menus
Shortcut keys

What does the file reference look like from where you invoke it.

Trying to understand why the macro is okay in one worksheet, but
not in the other. Do you have Event macros that are installed
with a worksheet. Does the following show a macro in the one of the worksheets that works.
Right-click on worksheet tab, View code
--

 

[WayneB]

Hi Dave: When I copy the sheet with macros from an old file to a a new file
and invoke it with a button it will spawn the old file. When I move the
sheet, it just says the macros don't exist. Here's the sample code...


Sub SortBDescending()
'
' JobSearchSortDate Macro
' Macro recorded 9/19/2004 by Wayne and Debi
'

'
Range("B2:I1000").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B2").Select
End Sub

 

[David McRitchie]

Hi Wayne,
I think you've got it **the button**.

If you use buttons or menus and have more than one workbook those
menus and buttons should be referring to your personal.xls macros.
Watch out when you restore your personal.xls workbook because it can
cause the same problem pointing to an old pathname.


Look at the macro assigned to the button, I think you will find the
old workbook, or the old pathname assigned to the buttons.

If that is not the case, then the following probably won't help you today,
and you'll have to dig up more dirt.

if that was the case then correct them. You might want to read
Barhopper -- fixup for Restored Toolbars, and -- Listing of Menu Items
http://www.mvps.org/dmcritchie/excel/barhopper.htm

You may have to exit out of all visible workbooks and
unhide your personal.xls before correcting your toolbars, don't forget to
make a change to the workbook and then save your workbook,
when you reopen, hide your personal.xls workbook again. Don't know how much of all of that is actually needed but you'd think with
the toolbars in their own files none of that
extra work would have been needed.

 

[WayneB]

Thanks Dave, but I never use personal.xls for macros: Too much sharing of
files with colleagues. The workaround I came up is as follows: export the
macros from the source sheet, move the sheet to the destination workbook
(don't copy because then the macros will path back to the old workbook),
import the .bas file. thx