Automate Distribution Template

[former_member602692]

HI Experts,

Would like to check if there is an existing blog here to Automate Distribution Template that includes updating the variable member like Cost Center in the Distribution template and also identifying the folder where the file will be saved via excel interface using macro button.

Thanks,

Joseph

[former_member186338]

Sorry, but please correct your post! Instead of "Insert File" use correct "Insert Image" to make your post readable.

I will not answer questions with "Insert File"

[former_member602692]

HI Vadim,

It is just a sample: 1st screenshot is the sample distribution template with cost center as variable wish to be dynamic controlled in excel interface using VBA. Then the 2nd screenshot is the proforma of the excel interface that use can use to manipulate and trigger the distribution template.

1st Screenshot

2nd Screenshot

[former_member186338 · Accepted solution]

In general you can replace native distribution function with vba code.

In your simple case without scheduling you can write code to:

1. Open template file

2. Replace value in some cell linked to report axis with required cost center.

3. Refresh and Go Offline.

4. Save As to some name and required path.

5. Loop

I have done this in number of projects without issues! I was also able to distribute template with variable number of sheets: For example we have a property - Cost center owner and single person can own number of cost centers. My VBA code will create target workbooks for each owner with variable number of worksheets depending on number of cost centers.

[former_member186338 · Accepted solution]

jtuawin

Unfortunately, the code I have is much more complex then in the mentioned scenario and will not help. Try yourself and post your code in case of issues.