Bonsoir le forum,
Dans l'onglet "base" du fichier "source", je veux copier toutes les lignes qui contiennent le mois d'octobre 2013.
ensuite je veux les coller dans l'onglet "données" du fichier "trades-10-2013".
Ci-joint les 2 fichiers.
Ci-dessous les macros que j'ai mais çà ne marche pas.
Merci pour votre aide.
1 ère solution :
Sub datas()
Dim i, k As Integer
Dim WBK As String
Dim WBK2 As String
Dim ws As String
Dim ws2 As String
Dim mths As Variant
Dim mt, da, yr As Variant
Dim plage As Range, cells As Range
ws = "base"
ws2 = "trades"
WBK = "source.xls"
da = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
mt = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
yr = Year(Workbooks(WBK).Sheets(ws).Range("C1"))
WBK2 = "Trades" & "-" & mt - 1 & "-" & yr & ".xls"
Sheets(ws).Select
For i = 3 To 10
Select Case Workbooks(WBK).Sheets(ws).cells(i, 3).Value
Case " da & " - " & (mt - 1) & " - " & yr "
cells(i, 3).EntireRow.Copy
End Select
Workbooks.Open Filename:="C:\Lien\" & WBK2
Sheets(ws2).Select
Range("A2").Select
ActiveSheet.Paste
Next i
End Sub
2ème solution :
Sub datas2()
Dim i, k As Integer
Dim WBK As String
Dim WBK2 As String
Dim ws As String
Dim ws2 As String
Dim mths As Variant
Dim mt, da, yr As Variant
Dim plage As Range, cells As Range
ws = "base"
ws2 = "trades"
WBK = "source.xls"
da = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
mt = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
yr = Year(Workbooks(WBK).Sheets(ws).Range("C1"))
WBK2 = "Trades" & "-" & mt - 1 & "-" & yr & ".xls"
Sheets(ws).Select
mths = da & "-" & mt - 1 & "-" & yr
i = 3
With Worksheets(ws)
Set plage = Range("C3:C10")
For Each cells In plage
If cells(i, 3) = da & "-" & mt - 1 & "-" & yr Then
cells(i, 3).EntireRow.Copy
Workbooks.Open Filename:="C:\Lien\" & WBK2
Sheets(ws2).Select
Range("A2").Select
ActiveSheet.Paste
End If
Next cells
End With
End Sub
Dans l'onglet "base" du fichier "source", je veux copier toutes les lignes qui contiennent le mois d'octobre 2013.
ensuite je veux les coller dans l'onglet "données" du fichier "trades-10-2013".
Ci-joint les 2 fichiers.
Ci-dessous les macros que j'ai mais çà ne marche pas.
Merci pour votre aide.
1 ère solution :
Sub datas()
Dim i, k As Integer
Dim WBK As String
Dim WBK2 As String
Dim ws As String
Dim ws2 As String
Dim mths As Variant
Dim mt, da, yr As Variant
Dim plage As Range, cells As Range
ws = "base"
ws2 = "trades"
WBK = "source.xls"
da = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
mt = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
yr = Year(Workbooks(WBK).Sheets(ws).Range("C1"))
WBK2 = "Trades" & "-" & mt - 1 & "-" & yr & ".xls"
Sheets(ws).Select
For i = 3 To 10
Select Case Workbooks(WBK).Sheets(ws).cells(i, 3).Value
Case " da & " - " & (mt - 1) & " - " & yr "
cells(i, 3).EntireRow.Copy
End Select
Workbooks.Open Filename:="C:\Lien\" & WBK2
Sheets(ws2).Select
Range("A2").Select
ActiveSheet.Paste
Next i
End Sub
2ème solution :
Sub datas2()
Dim i, k As Integer
Dim WBK As String
Dim WBK2 As String
Dim ws As String
Dim ws2 As String
Dim mths As Variant
Dim mt, da, yr As Variant
Dim plage As Range, cells As Range
ws = "base"
ws2 = "trades"
WBK = "source.xls"
da = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
mt = Month(Workbooks(WBK).Sheets(ws).Range("C1"))
yr = Year(Workbooks(WBK).Sheets(ws).Range("C1"))
WBK2 = "Trades" & "-" & mt - 1 & "-" & yr & ".xls"
Sheets(ws).Select
mths = da & "-" & mt - 1 & "-" & yr
i = 3
With Worksheets(ws)
Set plage = Range("C3:C10")
For Each cells In plage
If cells(i, 3) = da & "-" & mt - 1 & "-" & yr Then
cells(i, 3).EntireRow.Copy
Workbooks.Open Filename:="C:\Lien\" & WBK2
Sheets(ws2).Select
Range("A2").Select
ActiveSheet.Paste
End If
Next cells
End With
End Sub