Button looses Macro setting when running Macro

[Dule]

The code below was intended to export a sheet from Excel into a new workbook
when a button is entered, then save and close the new workbook and return to
the original workbook.

This code ran without issue in Excel 2003 but with 2007 after the new book
is made, saved and closed, the macro button in the original book and all the
buttons with macros assigned in the orginal book dont work.

It seems that each button thinks their macro is associated with the new
workbook that was created and the path to the macro in the original was
changed. I have tried to find an answer or a workaround for this but have
had no luck.

Would anyone be able to offer me any advice?

Dule

Sub ExportSheet1()
'
ThisWorkbook.Activate
Sheets("Sheet1").Select

Sheets("Sheet1").Copy Before:=Sheets("Sheet1")

Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Move
Range("B1").Select

SaveExportedSheet1
ThisWorkbook.Activate

End Sub

Sub SaveExportedSheet1()

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlsm), *.xlsm")

If fileSaveName <> False Then
ActiveWorkbook.Save
ActiveWorkbook.Close
Else: Cancel = True

End If

End Sub

 

[Joel]

Try these changes

Sub ExportSheet1()

Dim newbk as variant

set oldbk = thisworkbook
' with oldbk
'create new workbook with one sheet
.Sheets("Sheet1").Copy
end with

set newbk = activeworkbook

Savebk(Newbk)

End Sub

Sub SaveExportedSheet1(Newbk)

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlsm), *.xlsm")

If fileSaveName <> False Then
newbk.Save
newbk.Close

End If

End Sub