Copying formula for multiple worksheets
Solved/Closed
Michelerj
Posts
2
Registration date
Tuesday June 9, 2020
Status
Member
Last seen
June 9, 2020
-
Jun 9, 2020 at 10:07 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 11, 2020 at 11:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 11, 2020 at 11:53 AM
Related:
- Copying formula for multiple worksheets
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Credit summation formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jun 9, 2020 at 12:12 PM
Jun 9, 2020 at 12:12 PM
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jun 11, 2020 at 11:53 AM
Jun 11, 2020 at 11:53 AM
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
Jun 9, 2020 at 12:29 PM