Passing Macro control to the sub

Azza

New Member
Joined
Oct 6, 2004
Messages
28
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
**********
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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
 
Upvote 0

stdz

Active Member
Joined
Sep 1, 2002
Messages
328
Hello Azza,

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

Ciao,

Don.
 
Upvote 0

Azza

New Member
Joined
Oct 6, 2004
Messages
28
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...

Code:
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.

Code:
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
 
Upvote 0

Azza

New Member
Joined
Oct 6, 2004
Messages
28
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
 
Upvote 0

Forum statistics

Threads
1,196,007
Messages
6,012,829
Members
441,732
Latest member
Ayon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top