Wildcard Sheet Name Dynamic

[cpadilla0024]

Hi,

I posted in Questions but I think this more applicable to the VBA section as I think it is very possible it will be needed. I have been taken to the cleaners over this sheet for the past couple days. What I am trying to do is create a sum across with all sheet that have "Resource" in the sheet name. The tricky part is that tabs get added and deleted and it would be great if the formula knew to calculate accordingly. For example, on the "SUMMARY" tab in C9, I want C9 added from all sheets that have "Resource" in the sheet name. And I would need this formula is a lot of the cells in that tab.

I was able to get a wildcard in but if I add in new sheets, it will not pick it up. Conversely, if I delete sheets, I do not want to get an error. Preferably, I would like to dodge VBA but if it must be, that works. I will just make a button for it.

I appreciate any help. Thanks!

 

[Marc L]

Hi !​

​

When the worksheets are contiguous a 3D formula can be applied like for example =SUM('Resource 1100:Resource 1400'!C9)​

as a 3d formula just needs the references of the first and the last worksheets of the contiguous block …​

So if those worksheets are not deleted and only new worksheets may be added between them - or deleted whatever - then​

you do not ever need a VBA code, do you ?​

​

 

[cpadilla0024]

Hi Marc,

Thanks for your response. Agreed in theory but I cannot control if or how they are moved. We usually try to keep them in order numerically. So, for example, if we have Resource 1000 added later, that would come before Resource 1100 and throw the whole thing off. Or if Resource 1100 ever went away, we would delete and it would probably have a reference error. That is why I think if we were able to say sum all C9's that have "Resource" in the sheet name, it would be solid. Another option could be for every sheet where A1 contains "Resource," sum c9. OR if we can do a VBA replace or something. I am not sure.

I would just hate to have to manually adjust these every time because I have a lot of contacts that have groups of Resources. Essentially I am checking their work and things have fallen through the cracks before and threw off our projections. Trying to make it more automatic.

 

[cpadilla0024]

By the way, your quote is amazing. I am going to have to steal that.

 

[Marc L]

So according to your attachment you must​

1. paste the below code to a general module like Module1
   
   
2. edit the SUMMARY C9 cell formula : =WSum("Resource *")

The WSum user defined function has a second optional argument for the range address to sum.​

When omitted like in the above formula the address is automatically added​

from the cell where the formula stands like if the formula is =WSum("Resource *","C9") …​

​

Function WSum@(Name$, Optional Zone$)
         Dim Ws As Worksheet, S@
         Application.Volatile
         If Zone = "" Then Zone = Application.ThisCell.Address
    For Each Ws In ThisWorkbook.Worksheets
          If Ws.Name Like Name Then S = S + Ws.Range(Zone).Value2
    Next
         WSum = S
End Function

Do you like it ? So thanks to click on bottom right Like !​

​

 

[cpadilla0024]

You are amazing! Thank you so much Marc!

It appears to work correctly. I will let you know if I run into any issues!