Change Sheet Tab Reference in Formula to Reference The same cells in another (last) Sheet Tab of the workbook.

kps73066

New Member
Joined
Apr 25, 2015
Messages
3
I hope I'm asking this clear enough without having to create a mini-sheet to reference.

This is an example of the type of formula in my spreadsheet that I don't know or can't find out how to automatically change and reference a different sheet using vba.
='06'!$D82 & "_" & '06'!$G82

Here's what creates the need for this. I have a Macro that selects the current 'last' sheet tab '06' using Sheets(Sheets.Count).Select .
When that macro is through running, '07' becomes the newest 'last' sheet in the file.

Is there vba code that I can use to change the '06'! in the formula to '07'! without having to manually type it into the cell each time I use the macro to add an extra sheet? The place I need to paste or place the new formula referencing tab '07'! is in a column directly next to the formula referencing tab '06'!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,132,982
Messages
5,656,220
Members
418,290
Latest member
ArrArkRE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top