Macro to hide columns and change formulas in multiple worksheets

[Catthy]

Hi I'm learning macros on the fly and have to admit probably go about it the wrong way. I have created a monster and the element of hiding columns does not work.
This is a cba model and the idea is to choose the length of time for a business case between 2, 3, 5 & 10 yrs. with one of these years selected I want the workbook to change the formula on some financial indicators in the 'Calculation' worksheet (this works) and hide relevant columns in the input and reporting worksheets. I only want to hide in case the there is a change in mind to the length of the proposal.

Unfortunately the workbook is too large to attach I did a cut down version but it is still too large. Some of this marco unhides and then hides worksheets as not all worksheets need to be visible in the initial use of the workbook this element work fine. The area not working is the highlighted in <Blue bold> and is repeated at the 3 year and 5 year code

Sub nYears()

Dim ws As Worksheet
   
 For Each ws In Sheets(Array("Financial Overview", "Investment Decision Report", "Stage Gate Investment", "I&E", "Cashflow"))
        ws.Visible = True
    Next ws
     
If Worksheets("Main Menu").Range("No.Years").Value = 2 Then
If Range("CalcTerminalValue") = "No" Then Range("TerminalValue").Formula = 0 Else Range("TerminalValue").Formula = "=Yr1NetCash / Discountrate"
Range("NPV").Formula = "=NPV(Discountrate,Calculation!M923:M923,V923)+Calculation!L923+Calculation!K923"
Range("ROI").Formula = "=SUM(NPV(Discountrate,M923:M923,V923)+L923)/-SUM(NPV(Discountrate,Calculation!M725:M725)+CFYr0NetEstab+Calculation!K725)"
Range("BenefitInvestmentRatio").Formula = "=SUM($K$727:$M$727,V727)/-SUM(K725:M725)"
Range("BenefitNetOperatingRatio").Formula = "=SUM($K$727:$M$727,V727)/SUM(K922:M922)"
[FONT=Arial Black]For Each ws In Sheets(Array("Investment Decision Report"))
        Range("G:N").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Stage Gate Investment", "I&E", "MarketAnalysis"))
        Range("F:M").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Cashflow"))
        Range("AD:AW").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Establishment Internal Staff", "Establishment External Staff", "Establishment I&E"))
        Range("AG:AZ").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Operating Income", "Operating Staff", "Operating Expenditure"))
        Range("AH:AB").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Capital Asset"))
        Range("K:R").EntireColumn.Hidden = True
    Next ws
    For Each ws In Sheets(Array("Quantifiable Benefits"))
        Range("I:P").EntireColumn.Hidden = True
    Next ws[/FONT]    

ElseIf Worksheets("Main Menu").Range("No.Years").Value = 3 Then
If Range("CalcTerminalValue") = "No" Then Range("TerminalValue").Formula = 0 Else Range("TerminalValue").Formula = "=Yr2NetCash / Discountrate"
Range("NPV").Formula = "=NPV(Discountrate,Calculation!M923:N923,V923)+Calculation!L923+Calculation!K923"
Range("ROI").Formula = "=SUM(NPV(Discountrate,M923:N923,V923)+L923)/-SUM(NPV(Discountrate,Calculation!M725:N725)+CFYr0NetEstab+Calculation!K725)"
Range("BenefitInvestmentRatio").Formula = "=SUM($K$727:$N$727,V727)/-SUM(K725:N725)"
Range("BenefitNetOperatingRatio").Formula = "=SUM($K$727:$N$727,V727)/SUM(K922:N922)"
For Each ws In Sheets(Array("Investment Decision Report"))
        Range("H:N").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Stage Gate Investment", "I&E", "MarketAnalysis"))
        Range("G:M").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Cashflow"))
        Range("AQ:AW").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Establishment Internal Staff", "Establishment External Staff", "Establishment I&E"))
        Range("AT:AZ").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Operating Income", "Operating Staff", "Operating Expenditure"))
        Range("AU:AB").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Capital Asset"))
        Range("L:R").EntireColumn.Hidden = True
    Next ws
    For Each ws In Sheets(Array("Quantifiable Benefits"))
        Range("J:P").EntireColumn.Hidden = True
    Next ws

ElseIf Worksheets("Main Menu").Range("No.Years").Value = 5 Then
If Range("CalcTerminalValue") = "No" Then Range("TerminalValue").Formula = 0 Else Range("TerminalValue").Formula = "=Yr3NetCash / Discountrate"
Range("NPV").Formula = "=NPV(Discountrate,Calculation!M923:P923,V923)+Calculation!L923+Calculation!K923"
Range("ROI").Formula = "=SUM(NPV(Discountrate,M923:P923,V923)+L923)/-SUM(NPV(Discountrate,Calculation!M725:P725)+CFYr0NetEstab+Calculation!K725)"
Range("BenefitInvestmentRatio").Formula = "=SUM($K$727:$P$727,V727)/-SUM(K725:P725)"
Range("BenefitNetOperatingRatio").Formula = "=SUM($K$727:$P$727,V727)/SUM(K922:P922)"
For Each ws In Sheets(Array("Investment Decision Report"))
        Range("J:N").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Stage Gate Investment", "I&E", "MarketAnalysis"))
        Range("I:M").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Cashflow"))
        Range("AS:AW").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Establishment Internal Staff", "Establishment External Staff", "Establishment I&E"))
        Range("AV:AZ").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Operating Income", "Operating Staff", "Operating Expenditure"))
        Range("AW:AB").EntireColumn.Hidden = True
    Next ws
        For Each ws In Sheets(Array("Capital Asset"))
        Range("N:R").EntireColumn.Hidden = True
    Next ws
    For Each ws In Sheets(Array("Quantifiable Benefits"))
        Range("L:P").EntireColumn.Hidden = True
    Next ws

ElseIf Worksheets("Main Menu").Range("No.Years").Value = 10 Then
If Range("CalcTerminalValue") = "No" Then Range("TerminalValue").Formula = 0 Else Range("TerminalValue").Formula = "=Yr9NetCash / Discountrate"
Range("NPV").Formula = "=NPV(Discountrate,Calculation!M923:V923)+Calculation!L923+Calculation!K923"
Range("ROI").Formula = "=SUM(NPV(Discountrate,M923:V923)+L923)/-SUM(NPV(Discountrate,Calculation!M725:V725)+CFYr0NetEstab+Calculation!K725)"
Range("BenefitInvestmentRatio").Formula = "=SUM($L$727:$V$727)/-SUM(K725:V725)"
Range("BenefitNetOperatingRatio").Formula = "=SUM($L$727:$V$727)/SUM(K922:V922)"
For Each ws In Worksheets
      ws.Cells.EntireColumn.Hidden = False
Next ws
End If

For Each ws In Sheets(Array("Financial Overview", "Investment Decision Report", "Stage Gate Investment", "I&E", "Cashflow"))
        ws.Visible = False
    Next ws

End Sub