Bonjour à tous,
J'ai adapté une marco trouver sur internet pour copier des données de fichier Excels et les coller dans un fichier a part afin de les centraliser.
Sub CopyDataBetweenWorkbooks()
Dim wbSource As Workbook
Dim shTarget As Worksheet
Dim shSource As Worksheet
Dim shCurrency As Worksheet
Dim strFilePath As String
Dim strPath As String
' Initialize some variables and
' get the folder path that has the files
Set shTarget = ThisWorkbook.Sheets("Summary")
strPath = GetPath
' Make sure a folder was picked.
If Not strPath = vbNullString Then
' Get all the files from the folder
strfile = Dir$(strPath & "*.xlsx", vbNormal)
Do While Not strfile = vbNullString
'Open the file and get the source sheet
Set wbSource = Workbooks.Open(strPath & strfile)
Set shSource = wbSource.Sheets("Summary Sensitivity")
Set shCurrency = wbSource.Sheets("Data")
'Copy the data
Call CopyData(shSource, shTarget, shCurrency)
'Close the workbook and move to the next file.
wbSource.Close False
strfile = Dir$()
Loop
End If
End Sub
' Procedure to copy the data.
Sub CopyData(ByRef shSource As Worksheet, shTarget As Worksheet, shCurrency As Worksheet)
Dim strRANGE_ADDRESS As String
Dim strRANGE_ADDRESS2 As String
If shCurrency.Range("E13").Value = "EUR" Then
strRANGE_ADDRESS = "B9:E9"
Else
strRANGE_ADDRESS = "B10:E10"
End If
If shCurrency.Range("E13").Value = "EUR" Then
strRANGE_ADDRESS2 = "B28:E28"
Else
strRANGE_ADDRESS2 = "B29:E29"
End If
Dim lCol As Long
Dim lCol2 As Long
'Dim fname As Long
'Determine the last column.
lCol = shTarget.Cells(shTarget.Rows.Count, "D").End(xlUp).Row + 1
fname = shTarget.Cells(shTarget.Rows.Count, "B").End(xlUp).Row + 1
lCol2 = shTarget.Cells(shTarget.Rows.Count, "D").End(xlUp).Row + 2
fname2 = shTarget.Cells(shTarget.Rows.Count, "B").End(xlUp).Row + 2
risk1 = shTarget.Cells(shTarget.Rows.Count, "C").End(xlUp).Row + 1
risk2 = shTarget.Cells(shTarget.Rows.Count, "C").End(xlUp).Row + 2
'Copy the data.
shSource.Range(strRANGE_ADDRESS).Copy
shTarget.Cells(lCol, "D").PasteSpecial xlPasteValues
shTarget.Cells(fname, "B") = shSource.Parent.Name
shTarget.Cells(risk1, "C") = "+10 bps"
shSource.Range(strRANGE_ADDRESS2).Copy
shTarget.Cells(lCol2, "D").PasteSpecial xlPasteValues
shTarget.Cells(fname2, "B") = shSource.Parent.Name
shTarget.Cells(risk2, "C") = "-10 bps"
' Reset the clipboard.
Application.CutCopyMode = xlCopy
End Sub
' Fucntion to get the folder path
Function GetPath() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.ButtonName = "Select a folder"
.Title = "Folder Picker"
.AllowMultiSelect = False
'Get the folder if the user does not hot cancel
If .Show Then GetPath = .SelectedItems(1) & "\"
End With
End Function
Cependant une fois sur deux la marco bug a cette endroit :
"Set wbSource = Workbooks.Open(strPath & strfile)",
Savez-vous pourquoi ?
Merci,
Cordialement,
Cabouse