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

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of the source and destination sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Priyanka298]

Hi mumps,

I am sorry for late response. I have upload my source file to dropbox and share link here.

https://www.dropbox.com/scl/fi/d5bwz4a8gpm9f6v07pa9s/74115.xlsb?dl=0&rlkey=j4j36fi15til1q7zeakd3ladu

I am sorry I can't share the destination file because of security.
Now, I have this source file saved in "C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE", I want to copy the data from this file to my master file. Master file is just a copy of all the data no formatting, no formula, just pasted values.

I have tried the code which I have uploaded, but it was only working when source file just has a values no formula and formatting. And I have many .xlsb files in the mentioned folder path.
I hope it make sense now.

Thank you.

 

[mumps]

Try:

Sub COPYPASTEQLD()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, wkbSource As Workbook, strExtension As String, LastRow As Long
    Set wkbDest = Workbooks.Open("C:\Users\Priyanka.Patel\Desktop\VBA\Portfolio\Portfolio Dashboard Week Ending 2022 04 08 - All.xlsx")
    Const strPath As String = "C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsb")
    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(wkbDest.Sheets("Active Proj - Detailed Report").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Priyanka298]

Hi mumps,

Thank you for your help, however this is not working.

I am sharing a link of destination file as well. So, you can get an idea.
Portfolio Dashboard Week Ending 2022 04 08 - All.xlsx

Thank you

 

[mumps]

The destination sheet name in the destination workbook you posted is "Active". When I changed that name to "Active Proj - Detailed Report" to match the sheet name in the code, the macro worked properly.

wkbDest.Sheets("Active Proj - Detailed Report").Cells(wkbDest.Sheets("Active Proj - Detailed Report").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

 

[Priyanka298]

Hi, mumps,

Yeah, I know but the destination workbook I posted is a sample of the real, and that's why I just put the sheet name as "Active".

I have tried the code on the original workbook and the sheet name is "Active Proj - Detailed Report".

 

[mumps]

I'm not sure why it isn't working for you. When I tested the macro it worked properly using the two files you posted. I assume that you are running the macro from a third workbook. Is this correct? Does the macro open the destination file and any of the source files?

 

[Priyanka298]

Yes, you are correct.

I am sorry I didn’t informed you about the macro file.

I have the macro file in different folder like inside the week 5 folder. Where the portfolio and source file folders are.

 

[mumps]

The location of the file containing the macro is actually not important. Does the macro open the destination file and any of the source files?