Hi,
I’m trying to process five files using a macro, and then find/replace the clean data to match actual sales to a spreadsheet with targets. I have a macro that does one file but I can’t work out how to process all files with the same macro. Any help appreciated.
Details:
In one file I have the target for each division based on full-year predictions:
Div. Item Area Route Target
Green Widget A Southeast Direct sale 2,500
Green Widget A Southeast Distributor 10,000
Blue Product X Northwest Distributor 5
I also have a set of files for each division. Each file has actual units sold in it by product, region and other criteria.
E.g.
Green.xlsx
Widget A-SE, Dir. sale, 1,089 units
Widget A-SE, Dist., 2,515 units
Blue.xlsx
Product X-NW, Dist., 10 items
I need to update the files to list each division’s target and actual sales together producing something like:
Performance.xls
Green tab:
Div. Item Area Route Target Actuals to date
Green Widget A Southeast Direct sale 2,500 1,089
Green Widget A Southeast Distributor 10,000 2,515
Blue tab:
Div. Item Area Route Target Actuals to date
Blue Product X Northwest Distributor 5 10
I have built a workflow that cleans up one Actuals file (Green) and does a find-replace on the Green target data.
What I would like to do is design a macro that will take each Actuals file in turn, run it through the workflow, find-replace it on the Target spreadsheet data (and then split it into tabs).
I’m having trouble getting started. Does anyone have a suggestion?
Thanks.
Solved! Go to Solution.
This is a quick reply as I might be missing some facts: try to see if you can avoid going down the macro route first. Could you for example do any of the following?
What I would try to do is to get the content onto the canvas using 1 of the above approaches and clean it based on the filename (make sure to output the filename as part of the input tool used). Then you can append the targets to it thereafter.
Let me know if I misunderstood you.
Thanks for the suggestion to do it without a macro. I think that will be the best way to go if possible.
My idea is to combine all the individual Actual Sales input files into one, process them into a format that I can match to the Target Sales file data records, and then find/replace to append the Actual Sales data to the Target Sales records.
I'll post again with the results or further questions.
Hi Tom,
I did a workflow as you suggested instead of a macro and it worked fine. Thanks for the idea.