Copying Data from Specific Sheet of a Multiple Workbooks to Specific Sheet of a One Workbook

[madhuchelliah]

Hello Folks, i need to copy datas from a specific sheet(Summary) of multiple workbooks in a folder to a specific sheet(Summary) of the parent workbook. I will run the macro from the parent file. I tried by best, it is beyond my basic knowledge. I leave it to you guys.
My inputs:
1. Sheets name is including parent workbook - Summary.
2. Copying Range from children workbook is from columns D5:I5 to rows used range.(pasting should be stacked after each sheets data.)
3. All the children files are in the Children folder which is under the parent workbook folder.
The data of all the workbook should be pasted continuously without any gap.
When the pasting of the each workbook starts, the name of the workbook should be offset to C column cell. For example if the paste start cell in the parent workbook will be always D5 so the pasted workbook name should be displayed in C5, like that if the next workbook paste starts in D10 then workbook name should be in C10. Please heads up. Thank you.

 

[mumps]

You mentioned earlier that both the source files and destination file were in the same folder. When you say

the macro should pick the workbooks in the children folder instead of destination folder

are you saying that they are in different folders? Currently, the macro will open the workbooks in the folder that contains the destination workbook. This means that they have to be in the same folder. Please clarify.

By the way, you don't have to use the "Reply with Quote" button each time you respond. Just use the "Quote" button. This keeps the response shorter.

 

[madhuchelliah]

Hello Mumps, my apologies if anything conveyed incorrectly. Source workbooks are under the Children folder which is in the destination workbook folder.

Please refer my first post.​

3. All the children files are in the Children folder which is under the parent workbook folder.​

Thanks for your efforts.

 

[mumps]

What is the full path to the folder containing the children files?

 

[madhuchelliah]

The path is "C:\Users\madhuchelliah\Documents\Parent Folder\Children Folder

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim desWS As Worksheet
    Set desWS = ActiveWorkbook.Sheets("Summary")
    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Dim fileName As String
    Const strPath As String = "C:\Users\madhuchelliah\Documents\Parent Folder\Children Folder\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        Set srcWS = srcWB.Sheets("Summary")
        With srcWS
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If desWS.Range("D" & desWS.Rows.Count).End(xlUp).Row < 5 Then
                desWS.Range("C5") = srcWB.Name
                .Range("D5:I" & LastRow).Copy desWS.Range("D5")
            Else
                desWS.Range("C" & desWS.Range("D" & Rows.Count).End(xlUp).Row + 1) = srcWB.Name
                .Range("D5:I" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1, 0)
            End If
            srcWB.Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub