Hi, I have a workbook that I'm working on that will tally up data from a generic sheet and sort it month by month up until the end of the financial year.
At this point if there is more data in the generic sheet then the workbook will build a clean copy of itself and start copying the data into the next month.
The problem I have is when the VBA code is initiated as the MAIN it will run and then open up a new book, I can pass control to the new book (SUB) until the sub closes the original book, doing so seems to end the MAIN and therfore the sub stops as well.
My question is . . . Is there a way to give full control to the called sub procedure so that if the original workbook is closed then the sub will keep processing. (and hence become the main without user intervention).
Thanks in advance
Az..
ps here is the guts of my code.
--------------
This is done from the "Currentbook" workbook
**********
**********
This is in the newly created workbook "New_Finanacial_Year"
-------------
********
The SortDataByMonth is another subroutine.
At this point if there is more data in the generic sheet then the workbook will build a clean copy of itself and start copying the data into the next month.
The problem I have is when the VBA code is initiated as the MAIN it will run and then open up a new book, I can pass control to the new book (SUB) until the sub closes the original book, doing so seems to end the MAIN and therfore the sub stops as well.
My question is . . . Is there a way to give full control to the called sub procedure so that if the original workbook is closed then the sub will keep processing. (and hence become the main without user intervention).
Thanks in advance
Az..
ps here is the guts of my code.
--------------
This is done from the "Currentbook" workbook
**********
Rich (BB code):
Sub Create_New_Workbook_Change_of_Financial_Year()
Dim new_financial_year As String
new_financial_year = Application.GetSaveAsFilename(InitialFileName:="New Financial Year", fileFilter:="Excel Workbooks(*.XLS),*.xls", Title:="Choose a filename for your New Workbook...")
.
.
.
Workbooks(new_financial_year).Save
Application.Run ("'" & new_financial_year & "'!handover"), new_financial_year
This is in the newly created workbook "New_Finanacial_Year"
-------------
Rich (BB code):
Sub handover(invar As String)
Dim new_financial_year As String
new_financial_year = invar
newbook = Workbooks(new_financial_year).Sheets("Sheet1").Cells(2002, 1)
currentbook = Workbooks(new_financial_year).Sheets("Sheet1").Cells(2003, 1)
Workbooks(currentbook).Save
Workbooks(currentbook).Close
this is where it normally stops.
SortDataByMonth
The SortDataByMonth is another subroutine.