CODEWITHSUNDEEP
vbaexcelexcel-formula
Chris L
Chris L

Reputation: 11

Pasting Values from Source Worksheet with Formulas

I am new to VBA and was trying to develop a simple macro that would automatically pull certain data from defined worksheets and ranges (always the same columns, varying rows) out of many different workbooks stored in a single folder and consolidate them in one master sheet by pasting the info into predefined columns with all the rows under one another. Through the help of this site I was able to develop the below code, which does exactly what I need. However, I am running into issues as the source information has many formulas, which, when moved to the master workbook, return '00000000' instead of the correct value. I believe the problem is due to the fact that my macro is trying to copy and paste the underlying formulas (if I change the source info to values it works fine) instead of converting them to values before pasting.

As such, what I think I need to do is modify my macro so that it only pulls the values from the source info and pastes those values in the master workbook.

My code is:

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Const strPath As String = "C:\Users\lci\Desktop\Project Work\Info Gathering\Master Data File\"
ChDir strPath
strExtension = Dir("*.xlsm*")
Do While strExtension <> ""
    Set wkbSource = Workbooks.Open(strPath & strExtension)
    With wkbSource
        .Sheets("Connectivity Path").Range("B8:P" & Range("B" & Rows.Count).End(xlUp).Row).Copy wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "AV").End(xlUp).Offset(1, 0)
        .Sheets("Overdraft Limits").Range("B8:H" & Range("B" & Rows.Count).End(xlUp).Row).Copy wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "BK").End(xlUp).Offset(1, 0)
        .Sheets("General And Bank Relationship").Range("B8:AU" & Range("B" & Rows.Count).End(xlUp).Row).Copy wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        .Close savechanges:=False
    End With
    strExtension = Dir
Loop
Application.ScreenUpdating = True

End Sub

Any help on how to resolve would be much appreciated. Alternatively, if my diagnosis of the issue is wrong (and it very likely is) any other ways to fix would be very welcome.

Thanks in advance!

Upvotes: 1

Views: 110

Answers (1)

Magnetron
Magnetron

Reputation: 8543

If you want to Paste values, you shouldn't use the destination parameter, but paste special:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Users\lci\Desktop\Project Work\Info Gathering\Master Data File\"
    ChDir strPath
    strExtension = Dir("*.xlsm*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Connectivity Path").Range("B8:P" & Range("B" & Rows.Count).End(xlUp).Row).Copy 
            wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "AV").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            .Sheets("Overdraft Limits").Range("B8:H" & Range("B" & Rows.Count).End(xlUp).Row).Copy 
            wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "BK").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            .Sheets("General And Bank Relationship").Range("B8:AU" & Range("B" & Rows.Count).End(xlUp).Row).Copy 
            wkbDest.Sheets("BAM Master Consolidated").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions