Macro to merge workbooks onto one worksheet

[MLS]

I have a folder containing a number of workbooks all with several sheets. I
need to copy the data from a particular sheet in each workbook onto a
template containing the heading row.

I have looked at all the posts on the discussion group and tried to go
through the steps on the recommended site
(http://www.rondebruin.nl/copy3.htm), but I can't seem to get it to work with
my case. It would be great if someone might be able to help as I am not that
experienced with VBA.

Facts:
1. I would like to browse to select the workbook folder
2. The sheet with data I need to copy is called "Holiday, sickness, etc".
The data I need to copy is in column A to D and starts in cell A5. It varies
in number of rows from workbook to workbook
3. The template I need to put it onto is called "Consolidated leave" (it
only contains one sheet) and is located in M:\Work\Country files\Country File
Templates. It already contains all the headings. The data needs to be
populated from cell A2
4. Once the template is populated I would like to save it as "Consolidated
Leave" in M:\Work\Country files\Country raw data files
5. I don't want the workbook file names to be listed on the finished file.

I know this is quite a mouthful, but I hope someone is able to help me solve
it.

Many thanks,

 

[Ron de Bruin]

Hi MLS

What is your problem when you try example 1 on the page

Above the page you can see how you can change it to start in the cell you want
and copy the all the data below it

We can change the code to copy to the template sheet but first try the example