How to copy data from four source workbooks to master workbook based on last row that was not previously copied

[MoonLove]

Dear friends,

I have a challenge on achieving the below project, kindly please assist:

- I have four source workbooks with names(GK,SK,RJ and TB)
- Each workbook(GK,SK,RJ and TB) have three worksheets with the same names(products, channels, and sales).
- I have another one workbook called consolidated workbook with the same worksheets names(products, channels, and sales) like those of the four source workbooks.
- I want a code that will copy data from each worksheet of all the four source workbooks and transfer/paste the data to worksheets in consolidated workbook based on the rows that were not previously copied from the last copy event.
- Currently I have the below code but whenever I ran it copies everything from worksheets on the source workbooks and paste to worksheets in consolidated workbook which result to duplicated data.

Sub Copy_From_All_Workbooks()
    Dim wb As String, i As Long, sh As Worksheet
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
                For Each sh In Workbooks(wb).Worksheets
                        sh.UsedRange.Offset(1).Copy   '<---- Assumes 1 header row
                            ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                        Application.CutCopyMode = False
                Next sh
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

What I thought is that the code would go and copy all data which have not been copied from the last copy event then let's say if I forgot to run the report yesterday, and I run it today evening, the code will copy all data from yesterday to today at once.

Oops, I got too focussed on how to get the "last date used" without changing too much of the existing code.
Just change the "=" to ">=" in the 2nd line below,

                        For isrc = 1 To UBound(srcArr)
                            If CLng(srcArr(isrc, 1)) >= lngDate Then

 

[MoonLove]

Dear Alex,

Bingo!! it has worked.

One last thing is when all data have been successfully pasted to the consolidated report I want the dates to be arranged in ascending according to dates.

See below:

 

[Alex Blakenburg]

You can get most of the sort details using the Macro Recorder.
The below relies on the arrshtnames having the right names.
Add to the end of the code after "Loop"

    Loop
    
    ' Sort sheets named in arrshtnames
    For i = 0 To UBound(arrshtnames)
        Set sh = ThisWorkbook.Worksheets(arrshtnames(i))
        sh.Sort.SortFields.Clear
        sh.Sort.SortFields.Add2 Key:=sh.Range("A2"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With sh.Sort
            .SetRange sh.Range("A1").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next i
    
    Application.ScreenUpdating = True
End Sub

 

[MoonLove]

It has worked.

Thank you so so much Alex.

Thank you a lot.

You have helped me a lot on this journey. I really treasure and appreciate this.

Have a great weekend.

 

[MoonLove]

Hi Alex Blakenburg,

Good day to you...

I got one of the scenario here I really need your help.

I cleared January data from the consolidated workbook so that I can start to copy Feb source data from source workbooks and paste to consolidate workbook.

After clearing these January data from consolidated workbook, I run the script, unfortunately the script don't copy data from source workbook to consolidated workbook and it does not return any error.

What is the cause of this? Kindly please assist.