katherine_nicccc
New Member
- Joined
- Mar 31, 2024
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
Hello VBA experts,
I have a workbook with tons of formula similar to below but with different column and row number.
e.g.
'=SUM('2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'
And every year, when there is new data e.g. 2023, i have to add in data from 2023 worksheet like below
'=SUM('2023!W6,'2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2023!W6,'2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'
This process feels very manual and time consuming. I am wondering if i can use find and replace function to replace this manual process.
I am thinking of vba codes to
But i am having trouble designing this vba. Any help appreciated! Thankuuuuu
I have a workbook with tons of formula similar to below but with different column and row number.
e.g.
'=SUM('2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'
And every year, when there is new data e.g. 2023, i have to add in data from 2023 worksheet like below
'=SUM('2023!W6,'2022!W6, 2021!W6, 2020!W6, 2019!W6, 2018!W6)'
'=SUM('2023!W6,'2022!Z6, 2021!Z6, 2020!Z6, 2019!Z6, 2018!Z6)'
This process feels very manual and time consuming. I am wondering if i can use find and replace function to replace this manual process.
I am thinking of vba codes to
- find all the cells with '2022' in their formula
- for each cell found, expand the formula to 2023!xx
- where xx, which is the row and column number e.g. W6, Z6 and etc, need to taken from the existing cell formula
But i am having trouble designing this vba. Any help appreciated! Thankuuuuu