Copy data from multiple workbooks to a master workbook using VBA

[Priyanka298]

Hi there,
I have tried the following code to copy and paste the data from source file to destination file, where the code works perfectly if the source file's record are just as value. But in my data, I have multiple files and that files contain records as formatted cells(have attached the image). I am not sure what's wrong in my code. Any help appreciated.

[
Sub COPYPASTEQLD()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = Workbooks.Open("C:\Users\Priyanka.Patel\Desktop\VBA\Portfolio\Portfolio Dashboard Week Ending 2022 04 08 - All.xlsx")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE\"
    ChDir strPath
    strExtension = Dir("C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE\")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("PDR Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets(1).Range("A3:BD" & LastRow).Copy
            wkbDest.Sheets("Active Proj - Detailed Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    
    Application.ScreenUpdating = True
End Sub

]
Also whenever I tried to open my Source file one message window pop-up. First image shows the error window, second and third is the image of my source file, if you check the cells contains some formulas for values. 

Many Thanks
Priyanka

 

[Priyanka298]

Nop

 

[mumps]

The original path you were using in the code was: "C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE\"
Are you now using this path: "W:\AAA PMO\-PM Reporting\2022\06 June\Week 2\ACT\"

 

[Priyanka298]

Yes

 

[mumps]

To be honest, I can't say why the macro isn't working properly or where ".DS_Store" is coming from. Try re-naming the ACT folder and see if you get the same error.

 

[Priyanka298]

All good.
Thank you so much