VBA run a macro that runs other macros chosen from a drop downlist

Question

i have been working on something for work in my free time. I have got so far and now got stuck. Basically I have six sets of data (School, Holiday, Bank Holiday, Saturday, Sunday and Boxing day) that get copied onto a new work book each week. e.g

Sunday = choice of Sunday or Boxing day
Monday = choice of school or holiday or bank holiday or boxing day  
Tuesday = choice of school or holiday or boxing day
Wednesday = choice of school or holiday or boxing day
Thursday = choice of school or holiday or boxing day
Friday = choice of school or holiday or boxing day
Saturday = choice of Saturday or boxing day

The idea being that every Saturday night the supervisor chooses the data needed for each day of the next week (via data validation cells), then clicks a button and the macro runs. See image:View of drop-down lists

I have set up 6 macros to copy the data across and I'm trying to setup a main macro that runs on the click of the "Create VAS" button. So far I can get it to work when I test using Application.run (see Sunday code), but as soon as I use the If or If Else it runs, but just runs the first macro in the list. For example Sunday works fine and copies the data across to the new workbook, but Monday renames the sheet and then always copies the school data (first option in the list) then skips onto the next step. I guess something is wrong with my Monday drop down selection. I need the Create VAS macro to run, then run the chosen macro, then go back to where itself was and carry on running its self if that's possible?

I have been teaching myself VBA using forums and posts on this website and the internet and have slowly been getting better, but this has really got me stuck, any help would be appreciated!

Sub CreateVAS()
'Step 1 - Create VAS Workbook
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:= _
        "C:\Users\Tom\Desktop\VAS.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'Step 2 - Create Sunday
    Sheets("Sheet1").Select
    Sheets("Sheet1").name = "Sunday"
    Application.Run "CreateSunday"

'Step 3 - Create Monday
    Sheets("Sheet2").Select
    Sheets("Sheet2").name = "Monday"
    Dim macroNameMon As String
    macroName = Range("C6").Value
        If macroNameMon = School Then
            Application.Run "CreateSchool"
        ElseIf macroNameMon = Holiday Then
            Application.Run "CreateHoliday"
        ElseIf macroNameMon = BankHoliday Then
            Application.Run "CreateBH"
        ElseIf macroNameMon = Boxing Then
            Application.Run "CreateBoxing"
        End If
    Windows("VAS.xlsm").Activate
    Sheets("Monday").Paste Destination:=Range("A1")

'Step 4 - Create Tuesday
    Sheets("Sheet3").Select
    Sheets("Sheet3").name = "Tuesday"
    Dim macroNameTue As String
    macroName = Range("C8").Value
        If macroNameTue = School Then
            Application.Run "CreateSchool"
        ElseIf macroNameTue = Holiday Then
            Application.Run "CreateHoliday"
        ElseIf macroNameTue = BankHoliday Then
            Application.Run "CreateBH"
        ElseIf macroNameTue = Boxing Then
            Application.Run "CreateBoxing"
        End If
    Windows("VAS.xlsm").Activate
    Sheets("Tuesday").Paste Destination:=Range("A1")

'Step 5 - Create Wednesday
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet4").Select
    Sheets("Sheet4").name = "Wednesday"

'Step 6 - Create Thursday
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet5").Select
    Sheets("Sheet5").name = "Thursday"

'Step 7 - Create Friday
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet6").Select
    Sheets("Sheet6").name = "Friday"

'Step 7 - Create Saturday
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet7").Select
    Sheets("Sheet7").name = "Saturday"
    Application.Run "CreateSaturday"


'Step 10 - Save all changes
    Windows("VAS.xlsm").Activate
    ActiveWorkbook.Save
    MsgBox "VAS Sheet created. Please rename and place in correct folder."
    ActiveWindow.Close

Please.... avoid using `SELECT`. [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Coding becomes much easier once you've rid yourself of that. — Darren Bartrup-Cook, Nov 19 '18 at 10:46
Is `School` a variable name? You use it as one in `If macroNameMon = School Then`. That whole `If..ElseIf` block could be replaced with a [Select Case](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/select-case-statement) — Darren Bartrup-Cook, Nov 19 '18 at 10:47
I'm also thinking your six macros could probably be one but with different arguments being passed to it - are the six macros similar? — Darren Bartrup-Cook, Nov 19 '18 at 10:51

Pᴇʜ · Accepted Answer · 2018-11-19T12:32:28.920

The following code considers School as a variable name not as a value:

If macroNameMon = School Then
    Application.Run "CreateSchool"

Consider changing it to

If macroNameMon = "School" Then
    CreateSchool '<-- this will call Sub CreateSchool() no need for Application.Run

Note that you can use Select Case which is easier that multiple If … Then … ElseIf …:

Select Case macroNameMon
    Case "School":      CreateSchool
    Case "Holiday":     CreateHoliday
    Case "BankHoliday": CreateBH
    Case "Boxing":      CreateBoxing
End Select

Also note that you mix up your variable names. You declare Dim macroNameMon As String but then you use macroName = Range("C6").Value.

I recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid wrong variable names.

Also get rid of all these .Select statements: How to avoid using Select in Excel VBA.

Instead of

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Select
Sheets("Sheet4").name = "Wednesday"

better use something like

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Wednesday"

So you would end up with something like that:

Option Explicit

Sub CreateVAS()
    Dim NewWb As Workbook

'Step 1 - Create VAS Workbook
    Set NewWb = Workbooks.Add 'remember the new workbook in a variable so we can easily access it
    NewWb.SaveAs Filename:="C:\Users\Tom\Desktop\VAS.xlsm", _
                 FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                 CreateBackup:=False

    'remove all sheets some Excels don't add 3 sheets but only 1. Therefore delete all to not run into odd issues.
    Dim i As Long
    Application.DisplayAlerts = False
    For i = NewWb.Sheets.Count To 2 Step -1
        NewWb.Sheets(i).Delete
    Next i
    Application.DisplayAlerts = True

'Step 2 - Create Sunday
    NewWb.Worksheets(1).Name = "Sunday" 'name first sheet
    CreateSunday

'Step 3 - Create Monday
    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Monday"

    Dim macroNameMon As String
    macroNameMon = ThisWokrbook.Worksheet("YourSheet").Range("C6").Value 'specify your workbook and sheet

    Select Case macroNameMon
        Case "School":      CreateSchool
        Case "Holiday":     CreateHoliday
        Case "BankHoliday": CreateBH
        Case "Boxing":      CreateBoxing
    End Select

    'the following syntax is wrong
    'NewWb.Worksheets("Monday").Paste Destination:=Range("A1")
    'it should be something like
    ThisWorkbook.Worksheets("yoursource").Range("A1").Copy Destination:=NewWb.Worksheets("Monday").Range("A1")

'Step 4 - Create Tuesday
    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Tuesday"

    Dim macroNameTue As String
    macroNameTue = ThisWokrbook.Worksheet("YourSheet").Range("C8").Value 'specify your workbook and sheet
    Select Case macroNameMon
        Case "School":      CreateSchool
        Case "Holiday":     CreateHoliday
        Case "BankHoliday": CreateBH
        Case "Boxing":      CreateBoxing
    End Select

    ThisWorkbook.Worksheets("yoursource").Range("A1").Copy Destination:=NewWb.Worksheets("Tuesday").Range("A1")


'Step 5 - Create Wednesday till Saturday
    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Wednesday"
    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Thursday"
    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Friday"

    NewWb.Worksheets.Add(After:=NewWb.Sheets(NewWb.Sheets.Count)).Name = "Saturday"
    CreateSaturday


'Step 10 - Save all changes
    NewWb.Save
    MsgBox "VAS Sheet created. Please rename and place in correct folder."
    NewWb.Close
End Sub

If your Create… procedures do nothing more than copy I suggest to use something like the following:

    Dim macroNameMon As String
    macroNameMon = ThisWokrbook.Worksheet("YourSheet").Range("C6").Value 'specify your workbook and sheet

    Dim SourceRange As Range
    Select Case macroNameMon
        Case "School":      Set SourceRange = Thisworkbook.Worksheets("School").Range("A1:N52")
        '… and so on
    End Select

    SourceRange.Copy Destination:=NewWb.Worksheets("Monday").Range("A1")

Thanks for the quick reply, when I change that code and run the macro using F8 it just skips down each IF not running any macros, then gets a 1004 error on the paste command (I guess because it has nothing to paste) — Tom, Nov 19 '18 at 11:02
@Tom Yes you need to copy something before you can paste it. Have a look at my final edit where you can see some improvements. — Pᴇʜ, Nov 19 '18 at 11:14
So I read up on.select, managed to get rid of them thanks to your suggestion and some googling! My code is cleaner now, but still doesnt work. The case thing just does the same as the IF ELSE, it just skips down the lines without running anything, almost if its ignoring the data validation in cell C6. With C6 highlighted I click data validation, entered the choices I wanted and double checked all the spelling, I then checked the spelling in the code and it all matches... Have I missed something? — Tom, Nov 19 '18 at 11:35
Using your example above, I have typed it all out (I never copy & paste, typing it out makes me think about what the code is actually doing & therefore hopefully I learn!) Where you have warned about the syntax being wrong is where I have a problem. CreateSchool is this: Sub CreateSchool() Windows("VAS New.xlsm").Activate Sheets("School").Range("A1:N52").Copy Windows("VAS.xlsm").Activate End Sub The main macro chooses which sub-macro to run, the sub macro copies the correct data then returns to new workbook & the main macro pastes on the correct sheet. is that right? — Tom, Nov 19 '18 at 11:53
@Tom If your `Create…` procedures do nothing more than `.Copy` then I suggest to omit them and just store that range in a variable you can use to copy. See my edit. — Pᴇʜ, Nov 19 '18 at 12:33
Thanks to your help, it is now working. After researching a bit more on using cases and setting workbooks and paths, I worked out how it all works. Thank you so much! — Tom, Nov 19 '18 at 12:44

VBA run a macro that runs other macros chosen from a drop downlist

1 Answers1

So you would end up with something like that: