VBA Macro to spool through multiple workbooks and paste special values into destination

[dtrynoski]

Hi, I'm creating a macro to spool through all workbooks I have saved in a folder and paste the results into a single file. I was able to find most the code I needed from this thread:

https://www.mrexcel.com/forum/excel...a-multiple-workbooks-into-master-sheet-2.html

The only problem I'm having is its not pasting values. How do I modify my code in order to just paste values into the resulting workbook?

Here is my code:

Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Users\dtryno\Documents\Projects\Excel Macro Development\FCWB Agg\Workbooks\"
    ChDir strPath
    strExtension = Dir("*.xlsm*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension, UpdateLinks:=False)
        With wkbSource
            If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
            LastRow = .Sheets("FORECAST").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("FORECAST").Range("E8:EE" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Try

With wkbSource
            If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
            LastRow = .Sheets("FORECAST").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("FORECAST").Range("E8:EE" & LastRow).Copy 
            wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            .Close savechanges:=False
        End With

 

[dtrynoski]

That worked, thank you!

 

[Fluff]

Glad to help & thanks for the feedback