Merge Single Worksheets from Different Files into One Workbook AND Replace Formulas

[Snoopy22]

Dear all,

As described in the title I like to merge merge single worksheets from different files into one workbook. The files are located in one folder and contain one or to worksheets with similar structure. I found this VBA Code below which works for me.

My question now is, how to alter the code in order to replace the formulas in the worksheets with their values before beeing inserted in the summary workbook? Something like paste special/values.

THANK YOU!
Pia
Sub CombineFiles()
'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
Dim i As Long

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

'This line will need to be modified depending on location of source folder
FolderLocation = "C:\Dokumente und Einstellungen\rw0mnp\Eigene Dateien\Datenanalyse"

'Set the current directory to the the folder path.
ChDrive FolderLocation
ChDir FolderLocation

'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
SelectedFiles = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)

'Create a new workbook
Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(FolderLocation & "\*.xls", vbNormal)

If IsArray(SelectedFiles) Then

For i = LBound(SelectedFiles) To UBound(SelectedFiles)
Set WorkbookSource = Workbooks.Open(SelectedFiles(i))
Set WorksheetSource = WorkbookSource.Worksheets(1)
WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
WorkbookSource.Close False
Next i

End If

WorkbookDestination.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[p45cal]

Try adding the 3 lines:

With WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
  .UsedRange.Value = .UsedRange.Value
End With

after the existing line:

WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)

Untested.

[Snoopy22]

Thank you very much! Works perfectly