Previous Topic Next Topic

Copying formula for multiple worksheets

Solved/Closed
Report
Posts
2
Registration date
Tuesday June 9, 2020
Status
Member
Last seen
June 9, 2020
 -
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
 -
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.

2 replies

Answer 1 / 2
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
 491
Hi Michel,

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
0
Report
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.
0
Answer 2 / 2
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
 491
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: 
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
0
Ask a question