3 files into one - help needed

[leanne2011]

Hi

I would like to automate the payroll reporting through macro code and need a bit of help on how best to approach it then build the code. So I would like to explain what I would like and see if someone can point me in the right direction on how to complete it.

I have 4 files as below:

1 = Main file called "final Data Sheet to be completed by macro.xlsx"
2 = "Test August T&A file.xls". This is a feed of absence codes, some data is needed and some isn't. The needed data is explained below.
3 = "Permanent Data.xlsx". This is the salary composition for an employee - all example data. salary, hrs, overtime rates etc etc
4 = "export payroll rpt sample.xlsx". This is a variable report which is exported out of payroll each month so it will vary month to month.

I need file 1 - final Data Sheet to be completed by macro.xlsx, for the fields to be populated from a combination of the above files through the following steps.

Steps =
A. Data populated in file 1/main file which exist in file 2/Test August T&A file (BEMS ID appear on all files which references each employee) for columns I to Z to be populated. EG. Earnings code XAP from the Test August T&A file, for the data to be populated in column with earnings code XAPL for each employee with their BEMS ID that match on the two reports.
B. If earnings code XAN appears in file 2, then for it to be converted to hours by looking up BEMS ID within file 3 and multiplying the "Daily Working Hrs" column D and pasting the results into file 1 under XANL column and respective BEMS ID row.
C. I need the payroll data transferred from file 4 to file 1. The headings do not match so on sheet 2 I have a mapping list so as this can be used as a reference. Eg. so tax on file 4 is actually named as Income Taxes on file 1 I therefore want this data populated in file 1 from file 4.

I realise this is a lot to ask, but a little direction is all that I am after to get me started and I will be sincerely appreciative. I attach the sample files - thanks in advance for any time that anyone can spare me.

[TMS]

I would start by recording as much as you can of the manual steps. For example, copying the files and inserting formulae. When you have the rough code, you can tweak it to cater for a variable number of rows, etc. Sounds as though you're going to make use of VLOOKUP to get details from the reference files.

It's not difficult in concept but clearly not a "quick fix".

Regards, TMS