click second icon at the top to Find Variant.

erase contect in "Created By" field

click green checkmark to lookup value

in the list that appears, choose "7100 ASF Aging (7100 ASF Aging Deb's Version" then click green checkmark (or double click)

Now the selection criteria is filled in

a. change "As Of Date" to current month end date

b. change "YTD Sales Through Period" to current month

click on the first button at the top or the F8 key to execute

The process takes about 90 seconds before it finishes

After the search is executed, the report appears on the screen

click the icon to export to excel

after about 20 seconds, the Save as Dialog will appear

name of the file, name does not matter.  use "7100 ar aging <date>.  Also the directory does not matter as this is a temporary file (but we could save it for history on the RPA server)

Then click Save

It will take about 30 seconds to save the file and Excel should open automatically and display the file.

You will now have the download SAP report open in Excel

You now need to open a second excel file.  the file will be the one used last month and we will use it and save it as the current month.

\\allmac.macdermid.com\MSP Finance (common)\CLOSING 2020\052020\7100\Reporting Info\AR May 2020 - CO 7100.xlsx

**  we can look into have a blank template or specific folders for the RPA process  **

when the excel files open, open the Summary tab

Copy previous month data into "Current" month on worksheet

Copy cells G35..I35  into cells G32..I32.  Use paste special - values only when copying

Now go to the Detail SAP tab.  This contains last months data that needs to be overwritten.

Go back to the other excel file that is open, the one that was downloaded from SAP earlier.

Copy the entire worksheet to the clipboard, but not the last line, not the totals

Now go back to the other speadsheet and on the Detail SAP tab, paste the data.  Paste - Values

Scroll to the end of the worksheet and remove any extra rows that were part of the previous data (in example row 3282 and below)

** may be better to blank out first, or use some sort of blank template for each month **

now old data is deleted

Column W needs to be preserved.  This does a vlookup to the Instructions Tab (hidden column T) to put the vertical name in the column.

** The user likes to make sure that every row has a value.  Not sure if needed or how to accomplish **

Now open the Pivot tab.  Select Analyze - Change Data Souce

ensure that the range matches what the newly pasted data on the Detail SAP tab is.

once confirmed, click the Refresh action

Now select all the data on the Pivot tab and copy it to the clipboard (rows 4 - 553 in example)

Select cell A2 and click Paste - Values

Scroll to the end rows and remove any values from the previous month.

In this example rows 552 - 559 need to be removed

On this tab we need to verify that the pivot table is selecting all the data that was just pasted on the Sum by customer tab.

Click Analyze - Change Data Source and verify the range

In this example A1..A551

the click Refresh

the spreadsheet is now complete.  The data on the Summary tab is now updated with the new data

Log into the HFM system by launched a web browser and going to https://hfm.esiglobalnet.com

Click on "PSPHFM"

Click on "Documents" under Application Tasks on the left.

Then open the "Input Forms" folder and select file "10a Aging AR LocalStat"

When the file opens, use the Entity drop down at the top to get the selection box

Use the search bar in the dialog box to search for "EP10036" then click Enter

Once "EP10036 - Macdermid US" is selected in the left pane, click the right arrow in the middle to update the Entity in the Member Selection of the right pane.  Then click OK.

Also ensure that the Year and Period are correct for the current month of this report

The Manual Input column can now be adjusted to the values that were arrived at on the spreadsheet.  So now go back to the spreadsheet and copy the cells F7 throught F15 into HFM.  Each value must be manually copied and pasted, you cannot copy and paste the range.

Once all the values are copied over to the Manual Input column, go to Actions - Submit Data

Process is now finished.  We need to Log out of systems and close programs.

Log out of SAP by clicking "Log Off" in the top right corner.  Then click "Yes" to confirm.  SAP will return to login screen.  Close the browser.

Go back to the SAP program.  Click the X on the top right to close the window.  It will then ask if you want to log off, click "Yes" to confirm.