suryasabniveesu
Member
I have attached two excel files 1) FormatAAR 2)FormatAAR - New
1st file is the report (Account Analysis Report) we generate from Oracle ERP and it contains a sheet namely"FNDWRR".
2nd file is the formatted form of 1st file which we do manually every time we generate from Oracle ERP as per the below are steps:
1) Create a copy of "FNDWRR" sheet and rename new sheet to "Workings" and Original sheet to "Master". Henceforth all changes will be done in "Workings" sheet only
2) Delete rows from A1:A25,
3) Select A2 ("Source") to last row where the report ends. In this case A2:R944. In other files it could be till any row,
4)Apply filter to above range and then filter "GL Date" and Blanks in G Column,
5) Delete all blanks and rows which have "GL Date" except the first row of the table,
6) Delete the columns N, O, P as these columns are not required,
7) Autofit the data in all columns,
8) Change the font type to Calibri and size to 11,
9) Unmerge the A2:A3 rows and delete A3 row,
10) Finally Save the file.
All above steps are being done manually whenever we generate an Account Analysis Report from ERP. Earlier I recorded a macro by doing all above steps (except Step 6) and created an Add-in file by assigning a shortcut key. But now that add-in is not serving the purpose as the new report in ERP is having 3 additional columns N,O,P and also the number of last row was fixed to a number. If the data range increases then execution of Add-in doesn't give desired output.
Request anyone to help me to create an add-in to format the original ERP report like the sample file (i.e FormatARR - New)
Thanks in Advance
Surya
1st file is the report (Account Analysis Report) we generate from Oracle ERP and it contains a sheet namely"FNDWRR".
2nd file is the formatted form of 1st file which we do manually every time we generate from Oracle ERP as per the below are steps:
1) Create a copy of "FNDWRR" sheet and rename new sheet to "Workings" and Original sheet to "Master". Henceforth all changes will be done in "Workings" sheet only
2) Delete rows from A1:A25,
3) Select A2 ("Source") to last row where the report ends. In this case A2:R944. In other files it could be till any row,
4)Apply filter to above range and then filter "GL Date" and Blanks in G Column,
5) Delete all blanks and rows which have "GL Date" except the first row of the table,
6) Delete the columns N, O, P as these columns are not required,
7) Autofit the data in all columns,
8) Change the font type to Calibri and size to 11,
9) Unmerge the A2:A3 rows and delete A3 row,
10) Finally Save the file.
All above steps are being done manually whenever we generate an Account Analysis Report from ERP. Earlier I recorded a macro by doing all above steps (except Step 6) and created an Add-in file by assigning a shortcut key. But now that add-in is not serving the purpose as the new report in ERP is having 3 additional columns N,O,P and also the number of last row was fixed to a number. If the data range increases then execution of Add-in doesn't give desired output.
Request anyone to help me to create an add-in to format the original ERP report like the sample file (i.e FormatARR - New)
Thanks in Advance
Surya