Copy specific rows from many files and paste into next empty row in master file

Hi everybody

I have a problem, and have not been able to find a working solution.

I have around 35 excel files in a folder and one master file in a different folder.

What I am looking for is a macro, which will copy the cells (A4:BD4) on the sheet named "Data" from alle 35 files, and then "paste special" only the value of all the copied cells (not the formula), into the master file in the next empty row in the sheet named "Ark1". Row 1-7 is used for headlines and other stuff, so the first possible empty row is row 8. The range is the same as the copied range: column (A:BD).

It would be nice, if the user is asked to select the folder in which the files are located. This folder will have a new name every month.

Best regards.

Rednalyn

Hi AB33

Thanks for your quick response.

The range I want to copy is only one row in each sheet. So I'll use the

Formula:

Please Login or Register  to view this content.

Is it possible to have the user select a folder, instead of hardcode the folder name?

Okay! But we first we need to test it if the code works as intended.

Will this code delete whatever data stored in row 8?

Formula:

Please Login or Register  to view this content.

The master file will be historic, so no data may be deleted. It should just paste into next empty row.

Ok, I'll test.

2 sec

Which Macro is it, I just saw there are severel in the document

Is it Consolidate og ImportFiles?

It works very well. It is even capable of copying from a lock sheet (I was worried it wouldnt do that, because the "Data" sheet is locked).

Her is the code:

Formula:

Please Login or Register  to view this content.

What will I have to change, in order to prevent it from deleting the already inserted data?
What will I have to change, in order to have the user specify the folder where the files are located? Can I maybe give a standard folder from where it starts, so the user will have the shortest possible way to search?

I Had attached the wrong file. There are two codes. One is called selectcode.
shtMy.Range("A8:BD" & Rows.Count).ClearContents

clears existing data in your ark sheet as you had said you have a header with 7 rows. If you do not want it, comment it and will change in to green.

Hi AB33

I've just used the code on "selectcode". It gives the user the possibility to select files in the folder. Can it be made, so that the user only has to select the folder, at not select all the files?

But there is also a problem. The code from "selectcode" will not paste anything. And it doesnt close the files after copy. The above inserted code, would do everything just right, except to give the user the possibility to choose a folder.

Formula:

Please Login or Register  to view this content.

You can select multi if you want, or a single sheet
Change

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

If you do not have data in selection, it will not copy. I have tested it on my sample and it works.

Please Login or Register  to view this content.

Thank you very much for your help and time