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]

Yes, macro open the destination file.

 

[Priyanka298]

Let me explain you, I have shared the copy of my source file and destination file, now in my original source file, the value in cell A3:G3 has some type of formula which is "=Start!B4", after that H3 has "=IF(Start!B11="Capped","T&M",Start!B11)", I3 has "=IF(G3="Very Small","Small Project","PSR")", and each cell containg some kind of formula.

I think this cause the problem in original file, I found that the files I have shared with you works perfectly but my original file wouldn't.

Many thanks,
Priyanka

 

[mumps]

The formulas should not be a problem. Unfortunately, I can't suggest a solution without seeing your original files. Perhaps you could de-sensitize the data in your original files leaving all the formulas and formatting exactly as they are and upload them. I wouldn't need all the data, just 10 or 12 rows would be enough to test the macro.

 

[Priyanka298]

Hi mumps,

Here is the link of my source file. This is just one file I have many files all the worksheet contains only one row data that is A3:BD.

74115 OMT Delivery Squads 220426.xlsb

Thank you
Priyanka

 

[mumps]

Replace the current part of the macro with this:

With wkbSource.Sheets("PDR Summary")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .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

 

[Priyanka298]

[/.Close savechanges:=False]
[/QUOTE]
this line shows an error: Run time error 438, Object doesn't support this property or method. 

However, it copies the data correctly, but opened the source file and with the dotted line around A3:BD row (the one we get when we copy something from cell)

 

[Priyanka298]

Hi mumps,

I tried this code on the same source file but this time I have added more .xslb files in my "C:\Users\Priyanka.Patel\Desktop\VBA\Week 5\QLD\- DONE\" folder, so it runs for the first file and give the error I have mentioned above and sand not running for the rest of the workbooks.

 

[Priyanka298]

I have removed that line, it works. But it keep all the source files open. I only want the destination file open at the end. Is it possible?

 

[mumps]

Try:

wkbSource.Close savechanges:=False

 

[Priyanka298]

it is working

Thank you so mumps.