[SOLVED] Copy data from multiple workbooks in a folder into one workbook paste special only value

Copy data from multiple workbooks in a folder into one workbook paste special only value

I want to Copy all sheets of multiple workbooks within a folder into another single workbook. I found below code but do not know how to paste special only values to avoid unnecessary formatting.

Sub GetSheets()

Path = "C:\Users\mechee69\Download\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets    
        Sheet.Copy After:=ThisWorkbook.Sheets(1)    
    Next Sheet  
    Workbooks(Filename).Close
    Filename = Dir()
Loop 

End Sub

Solution

Try the code below, it will PasteSpecial only the Values, if you want you can extend to copy also the Formats.

Option Explicit

Sub GetSheets()

Dim Path As String, Filename As String
Dim WB As Workbook
Dim Sht As Worksheet, ShtDest As Worksheet

Path = "C:\Users\mechee69\Download\"
Filename = Dir(Path & "*.xls*")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While Filename <> ""
    Set WB = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
    For Each Sht In WB.Sheets
        Set ShtDest = ThisWorkbook.Sheets.Add(After:=Sheets(1))
        Sht.Cells.Copy
        ShtDest.Name = Sht.Name '<-- might raise an error in case there are 2 sheets with the same name
        ShtDest.Cells.PasteSpecial xlValues
    Next Sht
    WB.Close
    Filename = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub