Related:
- Copying formula for multiple worksheets
- Copying formulas to multiple worksheets ✓ - Forum - Excel
- Protecting your formulas in multiple worksheets ✓ - Forum - Excel
- How to apply a formula to multiple sheets in excel - How-To - Excel
- How to auto change sheet reference in copied formulas - Forum - Excel
- If formula excel multiple conditions - How-To - Excel
2 replies
TrowaD
- Posts
- 2690
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 12, 2021
Hi Michel,
Not sure that can be done with a formula, so here is a code:
I did assume that you want to add a number in A10 on ALL sheets AND that all sheet are in order.
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
Not sure that can be done with a formula, so here is a code:
Sub RunMe()
Dim ws As Worksheet, x As Integer
For Each ws In Worksheets
ws.Range("A10").Value = 1600 + x
x = x + 1
Next ws
End Sub
I did assume that you want to add a number in A10 on ALL sheets AND that all sheet are in order.
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
Report
Michelerj
- Posts
- 2
- Registration date
- Tuesday June 9, 2020
- Status
- Member
- Last seen
- June 9, 2020
I tried that but it didn't work for me. I tried =MID(CELL("filename"),FIND("]",CELL("filename"))+1,100) on my template (BID) worksheet but it doesn't work correctly on the other worksheets. BID1, BID2, BID3. It copies the same formula that's on my template but doesn't update to the correct worksheet name.
TrowaD
- Posts
- 2690
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 12, 2021
Hi Michel,
Not sure what didn't work for you, but let's work with your formula.
Your formula just returns the sheet name and we want the last number of the sheet name.
Not sure for how many sheets you want to do this for, so I added an IF statement, so the sheet number will be retrieved for up to 2 digits.
Here is the formula:
=1599+IF(ISERROR(RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),2)*1),RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),1),RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),2))
Now the problem exists that the formula doesn't produce the right result, because it doesn't get recalculated automatically.
Implement the following code to solve that issue:
Instead of inserting a module and placing the code there, look at the left side and place this small snippet of code under ThisWorkbook.
Here is a visual aid:
Best regards,
Trowa
Not sure what didn't work for you, but let's work with your formula.
Your formula just returns the sheet name and we want the last number of the sheet name.
Not sure for how many sheets you want to do this for, so I added an IF statement, so the sheet number will be retrieved for up to 2 digits.
Here is the formula:
=1599+IF(ISERROR(RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),2)*1),RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),1),RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,100),2))
Now the problem exists that the formula doesn't produce the right result, because it doesn't get recalculated automatically.
Implement the following code to solve that issue:
Private Sub Workbook_SheetActivate(ByVal Sh As Object) ActiveSheet.Calculate End Sub
Instead of inserting a module and placing the code there, look at the left side and place this small snippet of code under ThisWorkbook.
Here is a visual aid:
Best regards,
Trowa
Subscribe To Our Newsletter!
