Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button looses Macro setting when running Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop running macro button, like Ctrl-Break button | Excel Programming | |||
Stop running macro button, like Ctrl-Break button | Excel Programming | |||
Stop running macro button, like Ctrl-Break button | Excel Programming | |||
Running a Macro through a button vs. VBA | Excel Discussion (Misc queries) | |||
excel to html looses button and macro, why? | Excel Discussion (Misc queries) |