I use ASAP utilities to insert multiple worksheets using a template. I'd like to create a formula that automatically inserts sequencial numbers. Ex: Sheet 1, cell A10, number starts with 1600. I want Sheet 2, cell A10, automatically insert 1601, Sheet 3, 1602, etc. I'm new with all this so any help would be greatly appreciated.
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.
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.
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:
