Snoopy22
03-27-2014, 02:58 AM
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
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