Argthtjtr

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

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:UsersTomDesktopVAS.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")