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! =)

 

[mumps]

You are very welcome.

 

[Gary24]

Hello Mumps,
This is a very good code.
Are you able to assist me in a similar code?

I have 6 files saved on a local drive (in a folder called Automobiles) and would want them to be added to a master workbook (master_file) in their specific worksheet. Each file has its own worksheet and I would want to copy everything from row 2 to the last row from the files and paste them into row 2 of each worksheet.

Example:
6 files (saved on a C Drive - automobile folder)
Car1.xlsx (will be pasted into car1 worksheet)
Car2.xlsx (will be pasted into car22 worksheet)
Car3.xlsx (will be pasted into car3 worksheet)
Car4.xlsx (will be pasted into car4 worksheet)
Car5.xlsx (will be pasted into car5 worksheet)
Car6.xlsx (will be pasted into car56 worksheet)

I would greatly appreciate your help on this issue for me. Thank you!

 

[mumps]

@Gary24: Welcome to the Forum. 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. Include a link to this thread if you think it is useful.

 

[niteshrishi]

Thank you so Much.. your code worked perfectly.

I had an extra space in one of the lines. Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    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
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[mumps]

I'm glad you found the code useful.

 

[niteshrishi]

Absolutely.. Cheers to you..
I want to learn complete VBA, don't know how to start.

 

[mumps]

A good place to start would be the very first post in this forum (highlighted in blue). Another would be this link posted by hiker95: https://www.mrexcel.com/forum/excel-questions/829893-macros.html#post4048295

Doing online searches and posting questions on this and other similar forums are also good resources. The more you use VBA, the better you get at using it.

 

[koehlkm12]

I had an extra space in one of the lines. Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    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
            LastRow = .Sheets("appendix B").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("appendix B").Range("C6:F" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Hi what would I do if I wanted to copy specific rows from the multiple workbooks based on a criteria in the master file. For instance I have many files with clients info and I want to open all the workbooks and only pull the rows where the specific clients name is in column A and be able to do this for many clients. So like I have a cell where I enter the client info then run the macro to open all the files in the folder and just pull their specific rows

 

[mumps]

@koehlkm12
Welcome to the Forum. According to Forum rules, you should not post your question in another member's thread. Please start your own thread. If you find this thread useful, you can post a link to this thread in yours. If you send me a private message with a link to your new thread. I'll be happy to have a look.

 

[Fatman003]

Hello @mumps. I am creating something similar to what it's in this thread. However, my master sheet doesnt have the same number of columns with the multiple workbooks I intend to copy data from (it contains some more columns ) So I'm wondering if it is still possible to transfer data from the workbooks to the master sheet while having those extra columns in the master remaining blank? I will appreciate your prompt response. Thanks!