VBA - Paste Values

[Nagin]

Hi,

I have the following code that extracts a range from multiple workbooks to a master workbook, it is working fine, however some of the cells in the selected range have formula's. I would like to paste values as currently it pulling through the formulas.

Sub CopyCityCare()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "folder location"
    ChDir strPath
    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
           
            .Sheets("Template").Range("A13:J19").Copy wkbDest.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
         

            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Thanks

 

[Hui]

Sub CopyCityCare()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "folder location"
    ChDir strPath
    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            .Sheets("Template").Range("A13:J19").Copy
            wkbDest.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Nagin]

thanks Hui that worked perfect, I am also trying write code when all the data I have extracted from each of the files I would then like for them all to be moved to another folder.

 

[Hui]

Ask that as a new question
Attach a sample file to make it easier for us