Copy data from multiple workbooks to one master worksheet

[jevi]

Hi All,

I did look online but I am not finding the solution that it works. I have a folder which have 20 o more files in xlsx where they have data just on the first sheet "Sheet1" which the columns are always the same from B4 to E as the row data might dipends..so one file might have 10 rows of data and another one 20 rows of data etc. I would like to copy all the data of this files just to one master sheet.

Thank you,

 

[vcoolio]

Hello Jevi,

Try the following:-

Sub Test()

            Dim Wb As Workbook, shM As Worksheet, lr As Long
            Dim Path As String, File As Object
            Set shM = ThisWorkbook.Sheets("Master")
            Path = "C:\Users\YOUR FILE PATH HERE"
            
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
            
            For Each File In CreateObject("Scripting.filesystemobject").GetFolder(Path).Files
                    Set Wb = Workbooks.Open(File)
                            With Wb.Sheets(1)
                                    lr = Wb.Sheets(1).Cells(.Rows.Count, "A").End(xlUp).Row
                                    Range("B4:E" & lr).Copy shM.Range("A" & Rows.Count).End(3)(2)
                            End With
                    Wb.Close False
            Next File
    
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I'm assuming that:-
- You have created a new Master file with the sheet to consolidate the data into named "Master".
- All files are in the same folder.

Be sure to insert your actual file path in the variable 'Path'.

I hope that this helps.

Cheerio,
vcoolio.

 

[jevi]

Hello Jevi,

Try the following:-

Sub Test()

            Dim Wb As Workbook, shM As Worksheet, lr As Long
            Dim Path As String, File As Object
            Set shM = ThisWorkbook.Sheets("Master")
            Path = "C:\Users\YOUR FILE PATH HERE"
           
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
           
            For Each File In CreateObject("Scripting.filesystemobject").GetFolder(Path).Files
                    Set Wb = Workbooks.Open(File)
                            With Wb.Sheets(1)
                                    lr = Wb.Sheets(1).Cells(.Rows.Count, "A").End(xlUp).Row
                                    Range("B4:E" & lr).Copy shM.Range("A" & Rows.Count).End(3)(2)
                            End With
                    Wb.Close False
            Next File
   
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I'm assuming that:-
- You have created a new Master file with the sheet to consolidate the data into named "Master".
- All files are in the same folder.

Be sure to insert your actual file path in the variable 'Path'.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, It is giving only a grey file in excel.....don't know what I am doing wrong. as I changed the path and named a file Master with sheet Master.

 

[vcoolio]

Hello Jevi,

It works just fine on my test files so I can't tell you what the problem is.
Completely delete the new master file that you have created. Manually create a new file and name it Master. Name sheet1 in this new file Master as well.
Ensure that ALL files, including the new Master file, are in the same folder. Double check the file path.
Test it again.

Cheerio,
vcoolio.

 

[jevi]

Hello Jevi,

It works just fine on my test files so I can't tell you what the problem is.
Completely delete the new master file that you have created. Manually create a new file and name it Master. Name sheet1 in this new file Master as well.
Ensure that ALL files, including the new Master file, are in the same folder. Double check the file path.
Test it again.

Cheerio,
vcoolio.

Ok it is working but is copying also the header of the files...I did put the header on master sheet at row 1 by myself as is the same, but is copying it in the second row....what should i do to fix it?

 

[vcoolio]

Hello Jevi,

In your opening post, you mention that your data starts in B4 of the source workbooks so now I'll assume that your data starts in B5 with headings in row4.
Hence, change this part of the code:

Range("B4:E" & lr)

to

Range("B5:E" & lr)

Cheerio,
vcoolio.

 

[jevi]

If the macro copies once the data, can't try it another time..it sound is not working as I am doing some trails. But once it worked. I did some formula on the master sheet but beyong column FGH so nothing to do with the macro but formula that would help for the job.

 

[jevi]

Hello Jevi,

In your opening post, you mention that your data starts in B4 of the source workbooks so now I'll assume that your data starts in B5 with headings in row4.
Hence, change this part of the code:

Range("B4:E" & lr)

to

Range("B5:E" & lr)

Cheerio,
vcoolio.

No it is right the data that I need to copy starts in all the sheets B4, B3 is the headings. But I did put the headings in the master sheet from A1:E1 and when it pastes the values it copies the headings again in the second row. That is not a big problem but better if not.

Than I saw that once it copies the data it doesn't do it a second time....and it sound is not working. Even if I delete the data at the master sheet and repeat the macro, it doesn't work as it sound it copied them once. is it possible?

 

[jevi]

With Wb.Sheets(1)

it gives this message of debug but it is working though. I don't understand why this message.

 

[vcoolio]

Hello Jevi,

I'm not exactly sure of or understand your last three posts. As I've previously stated, I do not have any problems on my end using some test files. Everything works as it should.
With this line of code,

Range("B4:E" & lr).Copy shM.Range("A" & Rows.Count).End(3)(2)

if your headings are in row3 then, as you can see, the copy starts at row4 which means that the headings are excluded. As long as you have data in the source workbooks and haven't deleted any data sets, the copy/paste will continue to work. You could try the same line of code slightly amended as follows:-

.Range("B4:E" & lr).Copy shM.Range("A" & .Rows.Count).End(3)(2)

but I don't think it will make much difference.

If you are continuing to have problems, it would be best if you uploaded a sample of your Master file and at least one source file to a file sharing site such as WeTransfer or Drop Box then post the link to your files back here. Make sure that the files are exact replicas of your actual files in all aspects and if your data is sensitive then please use dummy data.

Cheerio,
vcoolio.