Button looses Macro setting when running Macro
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
"Dule" wrote:
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 Befo=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
|