Dynamic Range for Multiple Sheets

Originally Posted by doubleuson

Can post workbooks as necessary.

Hi doubleuson, Would be appreciated if you can please do provide the sample workbook with expected results.

Originally Posted by doubleuson

'Group _ Details', 'Simple Group _ Details' and 'Group _ Data Report'

So basically these 3 worksheets will be based on each month separately for eg. Group _ Details_Jan, Group _ Details_Feb, Group _ Details_Mar and so on and you want to compile them in your Funders Data Report tab and from the Funders Data Report worksheet the data will be fetched in your Annual Summary tab basis on drop down selections. Am i on correct path.

Kindly confirm if i am at wrong.

Yep, sorry, that's an error in passing on my part.

doubleuson, but keeping the number in the tab name would be difficult to identify for which month the tab is. I feel instead of keeping numbers, months first 3 character would be helpful. Rest is upto you dear.

I am aware of this, but this is the way that the information is collected. Group number is the identifying characteristic rather than month. Regardless, any and all help would be greatly appreciated.

Thank you Neilesh!

Here is the most recent version of the workbook for you to use as you please. Again, any and all help is greatly appreciated.

doubleuson, In the provided workbook i can see only two tabs needs to fill manually which are Group_Details and Simple Group_Details and rest all are formulated and these two tabs will be added on monthly basis and based on monthly tabs rest other tabs will provide the output. Am i correct.

The ‘Simple _ Group Details’ sheet automatically transposes information from the ‘Group _ Details’. So, the only sheet which needs manual input is ‘Group _ Details’.

‘Group _ Data Report’ feeds directly off ‘Group _ Details’

Can you please provide some unreal data in the Group Details tab so accordingly i can have a view how things are flowing.

Sorry Neilesh, I didn't see this until just now.

Here is a version with false data inputs:

How is it coming along?

Thank you dear, Let me have a look. I just reached at work and would try to resolve.

I have, which is why I was surprised when there was a post I hadn't read and I hadn't been notified.

No matter, after all, this is free help I am receiving and I'm clearly in no position to do it on my own :D

Thank you again, Neilesh.

Hi Neilesh, so the workbook you have created generates 'Group [INPUT] Details' and 'Simple Group [INPUT] Details' sheets. Would it be possible to have the 'Group [INPUT] Data Report' created also?

This is perfectly on the right track. As long as the sheets can be generated with identical formatting as the one I have designed in my provided workbook.

Looking great so far, thank you!

Doubleuson try with putting the only the number or the month name in Doubleuson tab cell L1. Either number or the month name or any other text will be given that will be included in your 3 new worksheets name.

In my case it is perfectly working.

Try the attached. Is this fine to proceed.

Create Worksheets with Specific text.xlsb

I tried your function, and this is my result:

Screen Shot 2018-08-08 at 12.18.53.png

Upon clicking 'Ok', the 'Group 25 Data Report' gets deleted and leaves only two new sheets.

Also, just note that the new sheets should be 'Group [INPUTS] Details' and 'Simple Group [INPUTS] Details' rather than 'Group [INPUTS] Details Report' and 'Simple [INPUTS] Details Report'. I only mention this because of how the formulae interact with eachother.

Thanks Neilesh.

Ofcourse dear the moment you hit Ok the specific text contains worksheets will be deleted and that is the reason it is getting delete so if you do not want to delete then simply hit cancel. The message box input will only delete the unwanted worksheets. For eg. if you hit the "Create Worksheets" button for the same period input twice then the VBA script will create the new worksheets with default names like "Sheet1", "Sheet2", & "Sheet3". The moment you put specific text in the message box for deleting the specific text contains worksheet those will be deleted.

So if by mistaken you hit twice "Create Worksheets" for the same input then simply type "Sheet" in the message box so the newly created worksheets created with default names will be deleted.

Since you have asked to add the "Report" text in the last of tab names so i did. Please find here attached the revised one. Please do let me know is it fine now.

Create Worksheets with Specific text.xlsb

Yep all fixed now.

You're all good to proceed.

Thank you Neilesh.

Yes. Aside from column 'O' which will be inputted manually.

Which column in Group_Details tab would like to consider as Period or Month Criteria column for Drop Down selection in Simple Group Details tab. I can not see any period or month column in Group_Details tab.

Each new set of the three sheets corresponds to a new period/month.

For example, Group 25 Details will be for September, and will be calculated by Group 25 Data Report.

So, each Data Report will calculate based on the individual group. Then the annual summary will calculate the same way the Data Report does, but for all the details

Dear doubleuson, Since i am heading the Financial Reporting department to the Senior management on daily basis for the multiple regions of our organization so, request to you please have patience. I am trying to provide you the best as earliest as i can.

Neilesh, of course, this is all free help so work at your convenience. I am here as and when you need.

I thank you very much for all your hard work, now and in the future.