Creating Worksheets based on a matrix in a table

[Legacy 226937]

Hey All,

I'm looking for some guidance on how to approach creating worksheets based on a matrix in a table in my workbook. If

Background:
I'm building a reporting tool that runs a few stored procs (Stored on sql server) I built and then based on various user inputs it creates a fancy and unique workbook for one or all site.
Currently, when creating a new workbook for a site, I copy all 8 sheets for the report. Everything is working fine and dandy and I'm super proud of myself cool: ) with nothing that could stop me!

Goal:
Enter new requirement: copy only the sheets that the site needs based on whether or not they perform the service or not. I store this mapping/matrix in a table named Sites that similar to below (1 = performs service):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Service A[/TD]
[TD]Service B[/TD]
[TD]Service C[/TD]
[TD]Service D[/TD]
[/TR]
[TR]
[TD]Site A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site B[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Site C[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site D[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Site E[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*Service A is always 1
*There are currently 30 sites

I started using arrays a day or two ago and I'm just learning to use them, and maybe it's excitement of as I feel like this may be overkill/overcomplicating it, but I think the best approach is to store the Sites table in a dynamic array (as it could change in the future - more sites or changes to services) and create a second array to identify which worksheets to generate (each service has 2 worksheets so 9 columns (4 services * 2 sheets + 1 sitename)) - e.g. Site A, Service A = 1, then array2 would have shtServiceASummary and shtServiceADetails and so on. When I generate each report, I would loop through array2 to generate only those that exists in that array.

I was under the impression this may be faster to do all those calculations in the array from what i was reading. Also, I'm not entirely sure that would work since I'm new to arrays haha

Is this the right approach? Any suggestions on a better and easier approach haha Your thoughts would be appreciated. I apologize if it's a dumb question too!

Thanks and happy holidays,
J

 

[Legacy 226937]

Also, the worksheets templates are already created and, on creation, looped through for each site (change site name so formulas update etc.) and copied to a new workbook.

 

[Legacy 226937]

Figured it out! This can be closed.