Passing Macro control to the sub

[Azza]

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
**********

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"
-------------

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.

 

[colbymack]

I am not 100% sure that it will work, but try executing a time-based macro in the SUB from the MAIN. Make it launch a couple of minutes after you would expect the Main to close (i.e. current time + 2minutes). Again, I am not sure if this will work, but I know that you can launch 1 macro from another without issue.

HTH

 

[stdz]

Hello Azza,

How are you putting the code for "Sub handover", into the new workbook ? Where is the code which does that ?

Ciao,

Don.

 

[Azza]

Hi, thanks for the replies. Stdz, the way I put the code into the new workbook is just to duplicate the existing workbook and then clear its contents...

new_financial_year = Application.GetSaveAsFilename(InitialFileName:="New Financial Year", fileFilter:="Excel Workbooks(*.XLS),*.xls", Title:="Choose a filename for your New Workbook...")
workbooks(currentbook).save
workbooks(currentbook).savecopyas "new_financial_year"
workbooks.open new_financial_year
new_financial_year = activewindow.caption

then a series of data that needs to be cleared from the copy - this goes on for every month but I'll just show one.

With Workbooks(new_financial_year).Worksheets("July")
    .Range("A11:AB30").ClearContents
    .Range("A41:T60").ClearContents
    .Range("A71:T90").ClearContents
End With
.
.
.
Workbooks(new_financial_year).Save
Application.Run ("'" & new_financial_year & "'!handover"), new_financial_year

Cheers Az

 

[Azza]

I have come up with a solution for my problem but I would still like to know if a called subroutine can continue after the main has been closed off.

Colbymack thanks for the idea but in my case the program is used to just sort data as quickly as possible. I haven't tried your idea yet although it may work.

The solution is to have the Main (currentbook) maintain control by copying itself as described above but instead of clearing the contents of the newbook it will clear its own contents after renaming itself and the new book and copying the required data to a hidden worksheet.

like so...
workbooks(a).save
workbooks(a).saveas "b"
This in itself hands the control to "b" as it is now called "b" and any work on "a" can be done from within "b" by open "a" and refering directly to it.
workbooks.open "a"



Sometimes you just need to step back and it seems so simple.

Cheers Az