CODEWITHSUNDEEP
excelvba
Tom
Tom

Reputation: 13

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

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

Upvotes: 0

Views: 137

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 2

Related Questions