Each month I receive an Excel report. I want to combine all the monthly reports into one worksheet of one workbook.
The 1st step is to write VBA code to run in the personal macro workbook which will organise this report into an Excel table. The report is not organised into neat columns. It has been designed to be printed out, page by page and viewed as such.
The 2nd step is for the table to be attached to an existing (several previous months) table in another, target workbook. In this way an historical listing of all entries will be built up in the target workbook's worksheet. (In the target workbook, the table headers will have to be inserted one time only, manually before the intial entries are placed in the worksheet. Thereafter, the monthly data should simply be attached to the bottom of the previous month's data).
The key tasks at step 1 are to:
Move data for each entry which appears in the "wrong" column/field into a newly created one;
Eliminate report page headers and footers;
Concatenate items of data lying in different rows of the same column (e.g. lines of an address appearing in successive rows below each other, but all in the same column) and move them to a newly created column/field;
Allow for variation in the number of rows occupied by each entry (e.g., 2, 3 or 4 rows used by an address in one column);
Allow for blank cells in some entries.
The key task at step 2 is to move the data from the source workbook into the target workbook at the bottom of the historical rows of data.
The VBA code should be well written and annotated with comment lines describing clearly each step in the procedure.
Further possible work: I receive a number of other reports in the same manner and there will be a future need to carry out the same exercise for some of these.
I have taken some time to create a sample set of dummy data (names and addresses, etc.) by editing an existing report. I have uploaded the dummy workbook to this project website.
The reports come in the Excel 2 format, so any software policy restriction would have to be removed by yourself.
Note, unfortunately, the source workbook's worksheet name is not standardised in the same format each month. In practice, different people produce this workbook and, although generally, the worksheet name will be in the same format, e.g. "Report (07-10-2017-0344-07).XLS", that format can vary from month to month.
So far, the fields to be used as table column headers in the target workbook that I have identified in the Excel report are:-
Source of business
You may have a different view on the fields/columns that are required.
I look forward to your quotation.
12 freelancers are bidding on average £66 for this job
I can do it for you quickly and exactly . Please contact me Relevant Skills and Experience I have finished great than 60 project in this site. Please see my portfolio for more detail Proposed Milestones £50 GBP - all
Dear client, I have expertise in VBA. Can show you previous projects on chat. I can do the task for you. Looking FOorward Relevant Skills and Experience VBA Proposed Milestones £50 GBP - Complete Excel file