Get UDF to recalculate without making it volatile or troubleshooting if it is

I hope I'm phrasing my problem adequately. I'm not a particularly experienced coder, and am really brand new to user defined functions, and this is my first time posting to this forum.

So, I've got a workbook SampleSheet.xlsx and a handy dandy User Defined Function:

Please Login or Register  to view this content.

If the Application.Volatile line is not commented out, the function works great for the most part, and updates as I add and remove sheets whose names contain "CB" to my workbook and change the information in these sheets. Since different projects will require different numbers of consultant sheets that folks will want to rename to something meaningful, I'm not sure how to pass that information into my function arguments in a way that would force recalculation. Help with this would be ideal.

But, I understand it is generally not a good idea to make UDFs volatile. Also, this brings me problems down the line. After I've done all my calculations, I also want to be able to do some data harvesting, by grabbing specific sheets from all workbooks in a folder and putting the values of the cells into worksheets in a new workbook. I've tried code that does paste special values and that just tries to set the values equal to one another (see code below, for privacy I put "..." instead of my network path in three places), and in either case the consultant billings column of my worksheet zeros out (unless it's not volatile). I can replicate this behavior manually without running all that code below by selecting all the cells in the worksheet, copying, and then trying to paste special --> values into a new worksheet twice in a row. It typically works on the first new worksheet, and then zeros out the consultant billings calculations on the second new worksheet.

All help is appreciated. Thanks.

-Beth

Please Login or Register  to view this content.

Thank you so much! This solves the problem!