Create a Macro to perform formula across all tabs in workbook

[rameezl17]

Hi Everyone,

I am trying to do a Addition Of an Index(Match,(Match formula across all my tabs in my workbook. For an example in excel the formula looks like this.

=INDEX('1'!$I$20:$O$24,MATCH(Database!A8,'1'!$G$20:$G$24,0),MATCH(Database!G1,'1'!$I$17:$O$17,0)) + INDEX('2'!$I$20:$O$24,MATCH(Database!A8,'2'!$G$20:$G$24,0),MATCH(Database!G1,'2'!$I$17:$O$17,0))

However in the future I will have more than just 2 tabs to do the formula above from. All the tabs that are created will keep going up in numerical order so 1,2,3,4, etc.

Is it possible to create a macro that will perform this action across all possible tab names in the future?

Thank you for your help.

Best Regards,

 

[rameezl17]

I want the formula answer to be on Sheets("Database").Range("B2")

I want the formula to give me the sums of all the Tab names that exist in the workbook (Tab1, Tab2, Tab3, etc)

 

[DataBlake]

I want the formula answer to be on Sheets("Database").Range("B2")

okay so you want the formula to be in one cell, but add to the formula based on how many sheets you have then?

 

[rameezl17]

Correct

 

[DataBlake]

Correct

oh okay the way you had worded it in your first post and title made me think you were trying to copy a formula through multiple sheets not vice versa
so in your initial formula

INDEX('1'!$I$20:$O$24,MATCH(Database!A8,'1'!$G$20:$G$24,0),MATCH(Database!G1,'1'!$I$17:$O$17,0))

explain A8 and G1 of database sheet
those cells are not locked so do you need them to change depending on how many sheets?
or perhaps this is enough to help you figure it out

Sub testerr()

Dim i As Long
Dim wsCOUNT As Long
Dim formPART As String
Dim formADD As String
Dim formFULL As String
Dim ws As Worksheet


'this counts all the sheets in your workbook and assigns it to wsCOUNT
wsCOUNT = Application.Sheets.Count


'this loops from sheet 5 to the end of your workbook
For i = 5 To wsCOUNT


'we use form to build your formula using dynamic strings
formPART = "INDEX('" & Sheets(i).Name & "'!$I$20:$O$24,MATCH(Database!A8,'" & Sheets(i).Name & "'!$G$20:$G$24,0),MATCH(Database!G1,'" & Sheets(i).Name & "'!$I$17:$O$17,0))"

formADD = formPART & "+" & formADD


'this goes through the loop
Next i

formFULL = "=" & Left(formADD, Len(formADD) - 1)

'this pastes the result of your formula to B2
Sheets("Database").Range("B2").Value = formFULL

End Sub

again i can't tell if this is what you want as i have no provided data to work with

 

[rameezl17]

Exactly what I was looking for thank you so much!!!!

 

[DataBlake]

Exactly what I was looking for thank you so much!!!!

glad i could help

 

[rameezl17]

Sorry to bother again... but I noticed that if the Cell Sheets("Database").Range( "A8") doesn't exist in one of the numbered tabs that the formula is summing from then it returns the answer as "N/A", is there anyway around that?

 

[DataBlake]

Sorry to bother again... but I noticed that if the Cell Sheets("Database").Range( "A8") doesn't exist in one of the numbered tabs that the formula is summing from then it returns the answer as "N/A", is there anyway around that?

what would you rather it return?
that just indicates that there was an error
maybe this can help?
https://support.office.com/en-us/ar...unctions-f91874c9-d30b-4b7a-8a6b-c622764a1992

if not i'm not super familiar with what your formula is doing in great detail

 

[rameezl17]

I believe I have to nest an IFERROR function within the macro

 

[DataBlake]

I believe I have to nest an IFERROR function within the macro

write your new formula with error handling (iferror or ifna) and then i will update the macro
or you can remove any sensitive data from your workbook and upload it to dropbox or use other methods of data sharing with mr excel found here