Novice user: Batch Macro to create new Excel workbooks and sheets?

[biciklanto]

Hi all,

I have an Excel sheet that contains several thousand rows of data about systems in use for various projects, as well as instances that have gone up and down in different months. Like this:

What I need to do:

• For each system in column A, I need to create a new Excel workbook (e.g., System_400.xlsx)
• For each month in column B, I need to create a new tab in that workbooks (e.g., Jan and Feb)
• For each Project and Product, I need to sum the instances (this is easy in the Summarize tool)

The end result would look like this:

I do NOT have a list of the systems in column A, so I think they would need to be enumerated (e.g., via Group By in the Summarize tool) to tell a macro what to do. I imagined getting that list and using it to feed a filter, selecting just the system in question for a particular Workbook, then using the field for the Data Output. But I don't really know my way around macros and they don't feel intuitive to me. I guess I need a batch macro because it should run for each system in the enumerated list, but after that I'm stuck. Help? 🙂

[wwatson]

I don't think this has to be a macro. I think you could do it with some sorting and a grouped render tool

[DavidP · Accepted solution]

You can also do it with the Summarize and Output data tool.

You just need to create a file and sheetname for each and use the 'Take filename from field' option in the output data tool.

Example attached.

[biciklanto]

Brilliant solution! I pulled it in, saw your logic, and was able to adapt it for the real workbook quickly and see how it works there (oddly enough, the real data isn't about Skydiving and Lilies).

It's funny to see as a novice that my mind goes straight towards complicated solutions, when a far more elegant and workable solution exists.

Thanks David!