Completed

VBA code required to convert monthly Excel report into an Excel Table

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:-

Account Code

Active

Client name

Client description

Created

Managing partner

Client Type

Source of business

Client address

You may have a different view on the fields/columns that are required.

I look forward to your quotation.

Thanks

John

Skills: Excel, Visual Basic

See more: convert word document table excel, convert word doc table excel doc, convert oracle report data excel, data entry, excel, convert oracle report text excel, convert word row table excel column, convert document word table excel table, standard monthly report engineering excel, convert report excel access vba code, vba code save web page html file excel, monthly report accounts excel, monthly report template excel, vba code convert html table excel, convert excel table vba code, create ms excel vba code to produce monthly expense report, monthly report engineering excel

About the Employer:
( 2 reviews ) Barrhead, United Kingdom

Project ID: #15421573

Awarded to:

J0e6011t

Hi John, from the description provided, i am able to script the macro accordingly to the step. However, may need further verification with more details. Thanks Relevant Skills and Experience visual basic application( More

£18 GBP in 3 days
(1 Review)
0.7

12 freelancers are bidding on average £66 for this job

£100 GBP in 1 day
(254 Reviews)
7.7
zebracaro

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

£50 GBP in 2 days
(110 Reviews)
6.5
Tanmoy236

"I am an Excel Expert with Professional experience working with Excel both with Formulas and Macro- VBA. Working with complicated formula and Developing Macro programs are part and parcel of my daily work. I am a Bus More

£90 GBP in 1 day
(131 Reviews)
6.2
£111 GBP in 1 day
(52 Reviews)
6.1
osmemon

I have 7 years of experience with 100% client satisfaction history. See my profile for client reviews about me. Yes my bid higher as this work require ample time and concentration. Relevant Skills and Experience Excel More

£166 GBP in 1 day
(34 Reviews)
5.6
scorpionobaid01

Hi I am here to assist u inthis project I am an ACCA member currently working in finance dept of Qatar Airways Have great skill and experience in Ms excel can assist u with high regards Plz share more info to proceed More

£94 GBP in 3 days
(16 Reviews)
4.0
sleepwarmer

You mainly need to move the rows into columns. This is a classic problem in excel. I can definitely help you solve this with a vba script. Relevant Skills and Experience I am an expert Excel vba/vb.net developer I bui More

£44 GBP in 2 days
(5 Reviews)
3.7
qasimalikgmail

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

£50 GBP in 2 days
(5 Reviews)
3.2
tuanthanhle

I can do it Stay tuned, I'm still working on this proposal.

£44 GBP in 1 day
(1 Review)
1.5
younoussthiam

A proposal has not yet been provided

£13 GBP in 1 day
(0 Reviews)
0.0
£10 GBP in 1 day
(0 Reviews)
0.0