VBA to copy data from multiple workbooks into master sheet

[excel_vba_1]

Hello Everyone!

I have to copy data from 10+ workbooks and paste it into a master workbook.
All the workbooks are located in a folder on my desktop: C:\Users\xbv\Desktop\group1

All the workbooks contain a sheet named 'appendix B', I have to open each workbook, go to sheet 'appendix B’, select columns range C to F starting from row 6 to row ‘x'(the last row can vary in each workbook), cntrl+v (copy), and paste the data range into master worksheet. In the master worksheet, I paste the data in Columns A to D and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A to D from every workbook in one sheet.

The columns range C to F and starting from row 6 always remains constant in all the sheets (appendix B ) of every workbook. Each workbook contains 7 sheets, but I am only interested in sheet ‘appendix B’

I have to repeat the same steps for 10-30 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a VBA code for this? I'm really new to VBA and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks! =)

 

[aidan_77]

Quick update here. I changed the name from Master to Sheet1 in both the workbook and in the code and now the program runs without any errors however I am not seeing any data populate. Not sure how to diagnose

 

[mumps]

See if this makes a difference:

Sheets("Sheet1").Range("B2" & Sheets("Sheet1").Range("O" & Rows.Count).End(xlUp)).Copy wkbDest.Sheets("Sheet1").Cells(wkbDest.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0)

 

[neebel]

Will the workbook name always be in column E of all three Master sheets? This would mean that all of the data in the source sheets is always in columns A to D. Is this correct?

@Uncle Josh: According to Forum rules, you should not post your question in another person's thread. Please start a new thread. If you send me a private message with a link to your thread, I'll be happy to have a look at it.

Hi mumps can you pls. solve my query which is similar to what you had solved in this thread. link for my thread is as below.

https://www.mrexcel.com/board/search/556819/

 

[Priyanka298]

Hi @mumps, I have followed this thread for my tasks, and have some questions, I have created a new thread and share the link of my thread here with you, https://www.mrexcel.com/board/threads/copy-data-from-multiple-workbooks-to-a-master-workbook-using-vba.1203859/

I am not aware how to message you privately, tried to find out options but not getting anything. It would be really appreciated if you could help me.

Many Thanks to you, and to all who helping me.

Priyanka

 

[SunshineVBA]

Hi Mumps,

I am still getting the same error on the same line:

Set wkbSource = Workbooks.Open(strPath & strExtension)

Please suggest. Thank you soo much for helping me.

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Users\xbv\Desktop\group1\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Appendix B").Range("C6:F" & Range("C" & Rows.Count).End(xlUp).Row + 1).Copy wkbDest.Sheets("Master1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Sheets("Appendix C").Range("D6:Y" & Range("D" & Rows.Count).End(xlUp).Row + 1).Copy wkbDest.Sheets("Master2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Sheets("Appendix D").Range("D5:I" & Range("D" & Rows.Count).End(xlUp).Row + 1).Copy wkbDest.Sheets("Master3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Don't forget to add the "\" to the path of the folder.

Hello,

This code worked perfect for what I was trying to do. But I im wondering if it is possible to add to it so that the name of the workbook the line items came from, appears beside all likes that were copied from those work sheets?

Thanks

 

[SunshineVBA]

Hello,

This code worked perfect for what I was trying to do. But I im wondering if it is possible to add to it so that the name of the workbook the line items came from, appears beside all likes that were copied from those work sheets?

Thanks

I created my own post. If you are able to help that’d be very appreciated!

Thread 'VBA code to combine multiple workbooks and record name'
VBA code to combine multiple workbooks and record name

 

[MadhukarG]

Hi Mumps,

The code works perfectly fine! I just had to had "\" to the path of the folder.


Thank you soooo much!! really appreciate it =)

Hi Mumps,

I have also need same macro, I have around 20 to 30 excel files and data is in one sheet named "Group Inventory input", need to copy data from cell C8 to E and data can be increase/decrease the rows and need to copy this data in master sheet from Cell C15 to E.
So I have used above code and pasted in the Master sheet inserting module but, getting error message as Runtime #9 and in the code it highlighted yellow the line in which there could be error.
Please can you assist if any change needs to update in the code. (I have just changed the folder path and sheet name Apendix b to Group inventory input).

 

[Fluff]

Duplicate to: Copy Data from Multiple workbook into one workbook

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.