Iteratively pull in workbooks for one workflow with common output

[JoshuaElphee]

I have 60+ workbooks which I need to run through a workflow, one at a time.  I have attempted utilizing the input  "filepath\*.xlsx" where each of the inputs are currently housed, however this appears to only run the first file through the process, despite the schema being exactly the same for each of the 60+ input files.

Within the workflow I (am attempting to):

1)  total a number of columns within a new row at the bottom

2)  pull in one row of data from a separate input file (added at the bottom of the excel sheet)

3) total these two rows in a third new row of data

4) add percentages for each of the totals within step 3

4.5) Following this, for each individual workbook, I need to iteratively add the totals from step 3 above into a new workbook (replicating the workbook utilized as an input in step 2 above).  I have not identified how to make this possible.

5) Save the new workbook in a new location, save the workbook that has appended information per step 4.5 above

6) Move to the next input workbook and start at step 1 above

I am not certain of how to best pursue these actions.  I have attached an example of what I am looking to do.  NOTE: while in the example these are separate sheets of the same workbook, in actuality the inputs and outputs are all separate workbooks.

A)"Input Example" - an example of one of the 60+ input workbooks as it currently stands

B)"Input 2 example" - an example of the workbook utilized in step 2 above as an input to each individual workbook (input example shows Job "ABC1" in column J; so the workflow reads "Input 2 example" and pulls in the totals in the row cooresponding with Job ABC1")

C)"Indv. output example" - example of the individual workbook outputs after being pulled in through process steps 1-6 above (totals from the current report provided below the data; Previous totals pulled from input 2 example workbook; these two rows totaled; percentages of obligations provided in last row)

D) "Final totals" - example of the workbook referenced in step 4.5 above (pulls the final total from each individual workbook into the spreadsheet as an appended row, saves and closes/waits for the next input workbook

None of this is currently working.  I believe I have errors in my logic throughout the workflow, however the largest of these currently is not being able to iteratively perform the same action for numerous spreadsheets (and save these individually/get the extra "totals" output workbook)

[acarter881 · Accepted solution]

@JoshuaElphee The error appears to be that the input does not contain "W81K" in the [job] field. 

Are you changing the input source? Please see the attached png file.

Also, it's difficult to be accurate without seeing exactly what's going on. It would be more efficient and effective to see how the workflow is setup.

[JoshuaElphee]

@acarter881 my recent attempts have been to pull in each workbook as a separate workbook (filepath is the same, however the workbook name is a random selection based on "Job + .xlsx"  The workflow appears to pull one or two of these, but fails on the others.  

I have altered the workflow utilized prior to this workflow (via the output tool), and now instead have each and every "Job" from my input filtered for and saved in a single output file - with each and every "Job" having its own tab.... for some reason each sheet is showing "Sheet1"+"Job", which I will work on (example, I have 45 sheets with the first showing "Sheet1ABC1", the second being "Sheet1XYZ7", etc.

If I can utilize this as an input, all of my issues may be mitigated - I will not need to pull in numerous workbooks iteratively, simply one workbook and iteratively perform the same actions each, with the final result being a separate workbook for each sheet.  

The attachment here is the workflow I wish to perform on each input file/sheet.  Note that I have altered column names/numbers, however I hope this aids in explaining what I am trying to do

[JoshuaElphee]

@acarter881 I have worked with your proposed solution significantly, and identified a few elements that I failed to present which were the crux of my original issue; utilizing your workflow with a few adjustments to meet my specific need, I have found reached the solution.

I will be accepting the solution and sending all of the upvotes for your time and effort assisting me (THANK YOU!).  

In the interim, I wonder if you can also assist on my next problem:

Utilizing the macro, I now have the workflow iteratively work through each "Job" and create a separate workbook for each.  Within each workbook, I have added a total for this report, a row for "previously incured total" (the total from the period prior to pulling the current report), and a row which shows these two rows added together (*Which I can the "CURRENT TOTAL" as it is the total of previous and current data).

Within my workflow, I would like to pull that "CURRENT TOTAL" row, and append it to a separate document.  IE. it will take this row and continue the current workflow, but will also open a file and paste these values accordingly in a manner that reflects the "Previous Data" sheet attached to my original question: 

Column 1 - "Job"  Column 2 - "Obligation"  Column 3 - "Area 1"  Column 4 - "Area 2", etc. 

can you assist in identifying a method which will open a file, and append this row of data, for each input file utilized?

[acarter881 · Accepted solution]

Hello, @JoshuaElphee.

Great to hear that you were able to work on finding improvements to your processes. You're very welcome.

I believe the intention of each thread in this section of the forum is to keep it self-contained (i.e., a single question with hopefully one clearly written solution).

I suggest making a new thread and providing some example inputs and outputs. It is fairly difficult and time consuming to create my own inputs and outputs based on a text description.

I believe your question is definitely possible, but unsure of the route I would take without more clarity.