Run multiple subroutines to completion with one button click

[dareman93]

This is kind of complicated, so let me see if I can do it justice with an explanation that doesn't take 10 pages. I am working on a scheduling workbook for another department at our firm. They use it to keep track of what everyone in that department is projected to work on over the course of the next week. They used to update it manually and asked me to automate a few of the tasks. That brings me to my problem.

The workbook consists of at least 4 worksheets:
-Sheet1 (Weekly Work Schedule-Current)
-Sheet2 (Lists)
-Sheet3 (Proposals)
-Sheet4 (Weekly Work Schedule-Last Week)

I do not want to prevent the department from adding other sheets to this workbook going forward and they should start at Sheet5 and count from there. The issue comes in when I try to automate the process of creating a new "Weekly Work Schedule-Current" worksheet.

The way I want it to work is that the current Sheet4 should be deleted. Sheet1 should then be copied and the copy should become the new Sheet4 and be renamed to "Weekly Work Schedule-Last Week". The subroutine will then go on to update everything on "Weekly Work Schedule-Current" for the next week. I can do all of these tasks individually, but I have not been able to come up with a way to do them all together.

The way I currently have it set up, the user will click a button on Sheet1 to start the process. This button click runs the following macro:

Private Sub Button_NewSchedule_Click()

    If MsgBox("This will delete the archived schedule sheet (Weekly Work Schedule-Last Week) " _
        & "and all data on that worksheet will be lost (the action cannot be undone)." & vbCrLf _
        & vbCrLf _
        & "The current schedule will become the new archived schedule sheet, dates on the " _
        & "current schedule sheet (Weekly Work Schedule-Current) will be updated for the " _
        & "next week, and data from the projected week on the current schedule sheet will " _
        & "be copied up to the current week.  Do you want to continue?", vbYesNo + vbQuestion, _
        "Create New Weekly Schedule") = vbYes Then
        Call NewWeek
    End If


End Sub

The NewWeek subroutine was then written to go through all of the tasks previously mentioned. The issue comes up from the fact that Excel does not seem to update the VBAProject Tree until all subroutines are completed. In other words, when the subroutine deletes Sheet4, the VBAProject Tree still shows it as an existing sheet. Then, when the existing Sheet1 is copied, the copy becomes Sheet5 (or Sheet6, Sheet7, etc. depending on how many other excess sheets are in the workbook) instead of becoming the new Sheet4.

I tried re-writing the NewWeek subroutine to separate out the various steps into other subroutines as follows:
- Delete the old Sheet4
- Copy Sheet1 to a new worksheet and rename it
- Run the rest of the tasks
I then called them subsequently through the Button_NewSchedule_Click button and ran into the same problem. Excel does not want to seem to update the VBAProject Tree while any subroutine is still open.

If I do those subroutines separately, it works just fine. The worksheet deletion subroutine deletes Sheet4 without a problem and, once that subroutine is done, the VBAProject Tree updates itself and shows Sheet4 as no longer existing (the workbook now consists of Sheet1, Sheet2, Sheet3, and Sheet5). I can then run the copy subroutine and the existing Sheet1 is copied and the new copy comes in as Sheet4. Finally, I can run the remaining steps in the NewWeek subroutine on Sheet1 to update it for the next week.

However, I have been unsuccessful in managing to combine them into one button click/task. Is there any way to do this (i.e. get the VBAProject Tree to update itself in the middle of a subroutine)?

For clarification, I have tried running this in debugging mode, where I get errors due to Sheet1 being copied in as Sheet6, and I have tried running it straight through in which case Excel crashes when it tries to set the wsArchive sheet to Sheet4.

I know I could probably get around this by setting wsArchive as the worksheet name (Weekly Work Schedule-Last Week) instead of using the absolute reference of Sheet4. But I want the code to be dynamic enough to account for someone changing the sheet name of Weekly Work Schedule-Last Week.

So, is there any way of getting the VBAProject Tree to be updated before all subroutines running are completed? Or is there a way of assigning multiple subroutines to a single button and running them in order without a VBA code being generated to call the subroutines in order (which results in the problem of the Tree not being updated)?

Thanks for the help!

 

[veryamusing]

Just curious, why is it necessary to delete "Sheet4"? Instead, would it be okay if the contents of Sheet4 were cleared and the contents of Sheet1 copied to it?

Sub copy()
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Worksheets("Sheet4")
    ws4.Cells.Clear
    ws1.Cells.copy ws4.Cells(1, 1)
End Sub

 

[Fluff]

I would tend to go with what veryamusing has suggested, but another option might be to delete sheet4, save the workbook, then copy sheet1.