Vba to change sheet name in all formulas on page based on cell refernce

[dsrt16]

I have a sheet called 2018, which tracks income, expenses by category, and taxes for that year.

I have a hidden sheet called tax report.

I have another hidden sheet called template, which is a template for the year sheet. When user closes out 2018, it will unhide the template, make a copy of it, change the copy's name to the new year (e.g. 2019), and rehide the template.

On the year sheet, when a user clicks on the button generate tax report, it will unhide the tax report and prompt user to print as pdf.

The tax report gives the total income for the year, the toral mileage for the year, and breakdowns the total expenses by category ( website, advertising, etc.)

When I crrated the tax report, the formulas refernce cells on sheet 2018. Many of them are named references.

However, when a user clicks on generate tax report on the 2019 sheet, the tax report opens up but will show the report for 2018. So I need the vba code that right now opens the unhidden tax report to also change all formula references on the tax report to the name of the sheet from which user clicked on the generate tax report button (the new year). The name of the year is in cell A1, so the vba code would just need to change the sheet name on every formula in the tax report to cell A1 on the sheet they clicked the button. (So if they clicked on generate tax report on sheet 2018, all formulas on the tax repoet sheet would referace 2018. If they clicked on generate tax report on sheet 2019, all formulas on the tax report sheet would reference sheet 2019, etc.)

This seems doable but then I don't know about the named references. (E.g. total category is one name, which refers to the category column in all 4 tables on the year sheet.)

I know how to use indirect to chage sheet reference for one formula, but I need all formulas on that tax report to change.

 

[Coding4Fun]

It was a little difficult to understand your exact setup but I think this should point you in the right direction.

I started by Creating a new workbook. I added 4 sheets to this new workbook (2018, 2019, Tax Report, Template). I then defined 2 named ranges, one in Sheet 2018 and one in Sheet 2019 both of which referenced Cells A1:A3. So on Sheet 2018 I created the named range "Range_2018" which represents range A1:A3 on that sheet. On sheet 2019 I created the named range "Range_2019" which represents A1:A3 on the 2019 sheet.

I then went to the Sheet Tax Report and in cells A1:A3 I entered the formula =Range_2018. This formula is what I was changing with the following code.

I added a button to both Sheet 2018 and Sheet 2019 and linked both buttons to this macro. This is to mimic the user pressing the button to generate the report from the year sheet.

Sub GenerateTaxReport()


Dim wsName As String


wsName = ActiveSheet.Name
Application.DisplayAlerts = False


Select Case wsName
    
    Case "2018"
        Sheets("Tax Report").Cells.Replace What:="Range_2019", Replacement:="Range_2018", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Case "2019"
        Sheets("Tax Report").Cells.Replace What:="Range_2018", Replacement:="Range_2019", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


End Select


Sheets("Tax Report").Activate


Application.DisplayAlerts = True


End Sub

 

[dsrt16]

Thanks. The problem here is there will be a ton of yearly sheets. It will keep going. 2020, 2021, 2022, 2023 etc. But they aren't created yet. A user creates them when they close out the current year.

Each new year sheet will be created from the template, so they will all have a generate tax report button.

When they click on the generate tax report button on say sheet 2019, then all the formulas on the tax report sheet need to be changed to reference sheet 2019. (Right now they are all referencing 2018). When they click on the generate tax report button on say sheet 2020, then all the formulas on the tax report sheet need to be changed to reference sheet 2020, etc.

The pages aren't created yet. So I don't think I could use your code because then I would have to have a case 2020, case 2021,etc. And again those sheets are not created yet. 2019 isn't even creating yet, just 2018. But the template worksheet is what a user uses to create each new year, so each new year sheet looks the same, with all the same type of information in the same cell.

So the tax report might have this formula in cell A3, for example, =2018!B9. However, if the user clicks on the generate tax report button from the 2019 sheet, then that formula needs to say =2019!B9. If they click on the generate tax report button from the 2020 sheet, then it needs to say =2020!B9. There 17 formulas on the tax report sheet that all reference sheet 2018 right now, but that needs to change with each new sheet.

Does that make sense?

 

[dsrt16]

Essentially I need some VBA code that says change all the sheet references on the tax report to the name of the active sheet.

 

[Coding4Fun]

Sub GenerateTaxReport2()

Dim wsName As String

wsName = ActiveSheet.Name

Sheets("Tax Report").Range("A3").Formula = "='" & wsName & "'!B9"
' List out the other 16 formulas that need to change

End Sub

 

[dsrt16]

Thank you. I will test that out.

 

[dsrt16]

Coding4Fun,

Sorry for late reply. I got slammed at work and had to put this project on hold. I tested out your code, and it works just fine.

Thanks!