Well I need a little help being a VBA novice.
I am attempting to save a sheet named Summary from a workbook named RPSDATAFILE.xlsm to a closed workbook named TestDataSheetASX1.xlsx before sheet 1.
I have used this vba code in Module 4 which appears to work –
Sub sb_Copy_Save_Worksheet_As_Workbook()
'Copies summary worksheet data upgrade to new workbook before sheet 1'
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Summary").Copy Before:=wb.Sheets(1)
Application.DisplayAlerts = False
wb.SaveAs "C:\Users\Mike\Desktop\TestDataSheetASX1"
wb.Close
ThisWorkbook.Activate
End Sub
The issue I have is that I am running a code in the open workbook RPSDATAFILE.xlsm that runs an application on time for other modules as below
Private Sub Workbook_Open()
Application.OnTime TimeValue("09:45:00"), "GetData"
Application.OnTime TimeValue("09:50:00"), "TestBeep()"
Application.OnTime TimeValue("11:59:00"), "GetData"
Application.OnTime TimeValue("12:05:00"), "TestBeep()"
Application.OnTime TimeValue("12:15:00"), "sb_Copy_Save_Worksheet_As_Workbook()"
Application.OnTime TimeValue("12:20:00"), "TestBeep1()"
Application.OnTime TimeValue("15:30:00"), "GetData"
Application.OnTime TimeValue("15:32:00"), "TestBeep()"
Application.OnTime TimeValue("15:40:00"), "sb_Copy_Save_Worksheet_As_Workbook()"
Application.OnTime TimeValue("15:42:00"), "TestBeep1()"
Application.OnTime TimeValue("17:00:00"), "GetData"
Application.OnTime TimeValue("17:05:00"), "TestBeep()"
End Sub
When the open workbook code runs it performs the first 4 application on times however when it runs the module to copy the sheet I get the following error message.
Cannot run the macro ‘sb_Copy_Save_Worksheet_As_Workbook() The macro may not be available in this workbook or all macros may be disabled.
I think that the macro loses focus to the open workbook code when it runs the copy.
Any suggestions would be appreciated.
Bookmarks