VBA Out of Stack Space Error 28

I have a drop down list with 8 options. When you select an option I want data from another sheet on the workbook to be copied and pasted onto sheet one. I have done this using 8 separate macro's I recorded. Any help would be great? See below my code:

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False

Select Case Range("F11")

Case "1. Texas Windstorm"
   Application.Run ("macro1")

Case "2. Louisiana Windstorm"
   Application.Run ("macro2")

Case "3. San Francisco EQ 6.7"
   Application.Run ("macro3")

Case "4. San Francisco EQ 8.1"
   Application.Run ("macro4")

Case "5. California Flood"
   Application.Run ("macro5")

Case "6. North East USA Windstorm"
   Application.Run ("macro6")

Case "7. Los Angeles Earthquake"
   Application.Run ("macro7")

Case "8. South Korea Windstorm"
   Application.Run ("macro8")




End Select
Application.ScreenUpdating = True

End Sub

Hi, and welcome to the forum. UnfortunatelyYour post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Since this is your first post I'll change it for you but please note for the future.

This is difficult to answer with certainty since we have no ideas what your macros are doing. I'm guessing that there is a lot of looping with variable values being popped into the memory stack without being removed thus overloading it. So upload your workbook so that we may see your request in context.

It's also likely that if you are using loops there might be a more efficient method using perhaps filtering.

Turn off events at the start of the code and turn them back on at the end.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Select Case Range("F11")
        Case "1. Texas Windstorm"
            Application.Run ("macro1")
        Case "2. Louisiana Windstorm"
            Application.Run ("macro2")
        Case "3. San Francisco EQ 6.7"
            Application.Run ("macro3")
        Case "4. San Francisco EQ 8.1"
            Application.Run ("macro4")
        Case "5. California Flood"
            Application.Run ("macro5")
        Case "6. North East USA Windstorm"
            Application.Run ("macro6")
        Case "7. Los Angeles Earthquake"
            Application.Run ("macro7")
        Case "8. South Korea Windstorm"
            Application.Run ("macro8")
    End Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

By the way, do you want this code to execute whenever any change is made in any sheet on any worksheet or only when a certain range/cell is changed?


PS Can you add code tags when posting code?

That code can be reduced to this.

Sub Macro1()
'
' Macro1 Macro
' Texas Windstorm
'

'
    Sheets("Damage Ratios").Range("ScenarioData[[1. Affected Area]:[1. Damage Ratio]]").Copy Sheets("Calculator").Range("E19")
End Sub

Just out of interest do the macros contain loops which are copying and pasting single rows each pass through the loop?

This code will only get fully executed if F11 has been changed.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Intersect(Target, Sh.Range("F11")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Select Case Target.Value
        Case "1. Texas Windstorm"
            Application.Run ("macro1")
        Case "2. Louisiana Windstorm"
            Application.Run ("macro2")
        Case "3. San Francisco EQ 6.7"
            Application.Run ("macro3")
        Case "4. San Francisco EQ 8.1"
            Application.Run ("macro4")
        Case "5. California Flood"
            Application.Run ("macro5")
        Case "6. North East USA Windstorm"
            Application.Run ("macro6")
        Case "7. Los Angeles Earthquake"
            Application.Run ("macro7")
        Case "8. South Korea Windstorm"
            Application.Run ("macro8")
    End Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub