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

Question

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

Possible duplicate of [Combine multiple Excel workbooks into a single workbook](http://stackoverflow.com/questions/26455076/combine-multiple-excel-workbooks-into-a-single-workbook) — ti7, Mar 18 '17 at 19:33

Shai Rado · Accepted Answer · 2017-03-19T07:47:06.553

1

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

edited Mar 19 '17 at 07:47

answered Mar 18 '17 at 18:21

Shai Rado

33,032
6
29
51

It served the purpose. Thanks. – mechee69 Mar 18 '17 at 19:27
@mechee69 you're welcome, please mark as "Answer ", by clicking on the **V** next to my answer, the check mark will turn green – Shai Rado Mar 18 '17 at 19:29
The resultant workbook changes the sheet names. Can you please edit code in such a way that new sheet names remain alike original sheet names. – mechee69 Mar 19 '17 at 02:41
@mechee69 see edited code, I've added a line for this. You should be aware you might get an error in case there will be an attempt to create a sheet with the same name of another sheet. – Shai Rado Mar 19 '17 at 07:47

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

1 Answers1