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 have this...im not sure if its the best way to approach but whenever I run the macro i get a subscript out of range and the line with the formula is giving me an error.


Sub Index_Match_Match()


Dim i As Integer
Dim ws As Worksheet


For i = 1 To 1000
Next i






For Each ws In Worksheets
If ws.Name <> "Database" & "Project Overview" & "New Project Template" & "Project Overview" & "Validation Lists" Then
Sheets("Database").Range("B2") = Application.WorksheetFunction.Index(Sheets(i).Range("G20:G24"), Application.WorksheetFunction.Match(Sheets("Database").Range("A8"), Sheets(i).Range("I17:DK17"), 0)) + Application.WorksheetFunction.Index(Sheets(i).Range("G20:G24"), Application.WorksheetFunction.Match(Sheets("Database").Range("A8"), Sheets(i).Range("I17:DK17"), 0))
End If

Next ws




End Sub

 

[DataBlake]

have you tired using the macro recorder to accomplish what you want?
that will at least tell you how to solve your formula issue

so here a few things with your code:
1. the i loop seems to do nothing so lets use that to count sheets instead of "each ws in worksheets"
2. you need a way to tell i to loop from 1 to the number of sheets. it looks like you want to exclude 5 sheets? where do these sheets appear in your workbook or rather are these sheets before or after your formula worksheets?
3. you need to treat each line as if it were its own command your VBA "formula" will not work. try using this "Range("B2").Formula = "FORMULA". that or you can explain the formula and i can try and convert it into VBA commands

so lets start there

 

[rameezl17]

Hi BlakeSkate,

1. How does the count sheets work? Will that automatically update as new sheets keep getting created?)
2 So the sheet order in my workbook goes like this - "Project Overview, "Validation Lists", "New Project Template, "Database" (where I want the formula to be held), "1","2", etc
3.The Macro recorder I tried looks like this

ActiveCell.FormulaR1C1 = _
"=INDEX('1'!R20C9:R20C15,MATCH(Database!RC[-1],'1'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'1'!R[15]C[7]:R[15]C[113],0)) + INDEX('2'!R20C9:R20C15,MATCH(Database!RC[-1],'2'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'2'!R[15]C[7]:R[15]C[113],0))"

It works great but i need it to loop through the worksheets with the number 1,2,3 etc. Which is where I tried to put the i but i mightve used it incorrectly.

 

[DataBlake]

Hi BlakeSkate,

1. How does the count sheets work? Will that automatically update as new sheets keep getting created?)

to answer your first question yes
to inquire about your # 2 will the formula change? the formula does not need to be "held" anywhere on a sheet if it does not change. ALSO these sheets will always start at the 5th sheet?

 

[DataBlake]

this should complete your task if you just want to add this formula to every sheet starting at sheet # 5

Dim i As Long
Dim wsCOUNT As Long
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 i to determine which sheet we want as our range B2 formula
Sheets(i).Range("B2").FormulaR1C1 = _
"=INDEX('1'!R20C9:R20C15,MATCH(Database!RC[-1],'1'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'1'!R[15]C[7]:R[15]C[113],0)) + INDEX('2'!R20C9:R20C15,MATCH(Database!RC[-1],'2'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'2'!R[15]C[7]:R[15]C[113],0))"

'this goes through the loop
Next i

i've added comments to help you understand as well

 

[rameezl17]

Sorry for taking so long to get back to you....

This works perfectly for the first 2 tabs ( "1", "2") however when I have tab "3", it doesnt include that tab in the formula...I need the formula to automatically find out how many sheets there are aside from the other 4 I listed and do the formula for all of them

 

[DataBlake]

Sorry for taking so long to get back to you....

This works perfectly for the first 2 tabs ( "1", "2") however when I have tab "3", it doesnt include that tab in the formula...I need the formula to automatically find out how many sheets there are aside from the other 4 I listed and do the formula for all of them

is tab 3 directly after tab 2? or is it before the 5th sheet in your workbook?
i've tested the code and it seems to work for me when i have 8 tabs
i have noticed that your code refers to '1 and '2 but does not include other tabs so maybe thats why.
why don't you now explain what you want this formula to do

do you have a #REF error in b2 on sheet 3?
also please come up with a better naming convention for sheets or its going to get really confusing really fast
at least something like formula1, formula2 etc

 

[rameezl17]

Yes "Tab3" comes directly after "Tab2" so it is the 7th sheet in my workbook. Yes the "1" & the "2" that the code refers to is the name of the sheet tabs.

So how this workbook works is that a person comes in and adds a new project(from the project overview tab). This new project will generate a new tab always adding to the end of the workbook. Currently there are 2 projects (Tab1, Tab2) this list will continue growing and the naming convention will automatically continue being (Tab1, Tab2, Tab3, Tab4, etc) once the new project button is clicked.

My goal is for the formula to Sum all the values returning from the Index, MATCH, MATCH, through all the project tabs (Tab1, Tab2 , Tab3, Tab4, etc) in my "Database tab starting at Cell B2.

I rearranged the code you sent me because I only need the formula for my "Database" tab since all the numbered tabs is what I ultimately want to feed into my "Database" tab.


Current code I have (works for Tab1, Tab2, not Tab3)

Dim i As Long
Dim wsCOUNT As Long
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 i to determine which sheet we want as our range B2 formula
Sheets("Database").Range("B2").FormulaR1C1 = _
"=INDEX('1'!R20C9:R20C15,MATCH(Database!RC[-1],'1'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'1'!R[15]C[7]:R[15]C[113],0)) + INDEX('2'!R20C9:R20C15,MATCH(Database!RC[-1],'2'!R20C7:R24C7,0),MATCH(Database!R[-1]C,'2'!R[15]C[7]:R[15]C[113],0))"


'this goes through the loop
Next i



Please let me know if this clarifies things a bit. Thank you for your help!

 

[DataBlake]

the reason this is not working is because you have changed
Sheets(i).range
to
Sheets("Database").range

do you want this specific formula to appear in your Tab1, Tab2, Tab3
or do you want it to be in your database?
i was under the impression you wanted it in your Tab sheets in the range of cell B2

is it that you want a formula for each sheet pasted in your database starting from B2 going down?

EDIT: if this is the case i will need you to post an example of your Tab1 as well as an example of the database. You can click the link in my signature to see the available tools for posting data. Without this data i can't efficiently test your formula and make it dynamic.