Bonsoir à tous,
débutante en VBA, je viens de m'inscrire sur ce site en espérant trouver quelques réponses à mon problème 
Je vous explique le contexte:
j'ai deux fichiers: un fichier source dans lequel j'ai de nombreuses données et un second dans lequel je récupere certaines donnée (je l'apellerais fichier target) du dossier source.
je souhaite quand une condition est remplis dans une colonne copier certaines valeur de la ligne correspondante dans mon second fichier. je veux que ce copier/coller ce réalise tant que la condition est vrai.
avant de réaliser cela je demande par une macro qu'elle aye chercher mon dossier source et qu'elle enregistre le fichier target par un nom.
je souhaiterais également que toutes mes macros s'exécute ensemble.
je vous copie ma macro dessous.
Est ce que quelqu'un pourrais me dire où j'ai fait une erreur car j'ai erreur 91 et erreurs de compliation et je ne sais pas où j'ai "merdé"
merci d'avance
Public wbSource As Workbook
Public wbTarget As Workbook
Sub main()
Dim stFileName As String
stFileName = Application.GetOpenFilename
If stFileName <> "False" Then
Workbooks.Open stFileName
stFileName = Right(stFileName, Len(stFileName) - InStrRev(stFileName, "\" ))
Set wbSource = Workbooks(stFileName)
Set wbTarget = ThisWorkbook
wbTarget.Activate
Application.Dialogs(xlDialogSaveAs).Show ("Beam KPIs Week " & Format(Date, "ww", vbMonday, vbFirstFourDays))
End If
End Sub
Sub Acceptance()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wbSource As Workbooks
Dim wbTarget As Workbooks
Dim QG_Tracker As Worksheets
Dim Acceptance_status_W As Worksheets
i = 4
j = 3
k = 3
While (wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value <> "" )
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value <= Date And wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value >= (Date - 6) Then
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 8).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 16).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 9).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 30).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 10).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 11).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 31).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 12).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 32).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 13).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 33).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 14).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 34).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 15).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 35).Value
wbTarget.Worksheets("Acceptance status W" ).Cells(j, 16).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 36).Value
j = j + 1
End If
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value > Date And wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value <= (Date + 7) Then
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 15).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 8).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 30).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 9).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 10).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 31).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 11).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 32).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 12).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 33).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 13).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 34).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 14).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 35).Value
wbTarget.Worksheets("Acceptance planned W" ).Cells(k, 15).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 36).Value
k = k + 1
End If
i = i + 1
Wend
End Sub
Sub Agreement()
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 4
j = 3
k = 3
While (wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value <> "" )
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value <= Date And wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value >= (Date - 6) Then
wbTarget.Worksheets("Agreement status W" ).Cells(j, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 8).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 30).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 9).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 10).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 31).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 11).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 32).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 12).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 33).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 13).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 34).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 14).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 35).Value
wbTarget.Worksheets("Agreement status W" ).Cells(j, 15).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 36).Value
j = j + 1
End If
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value > Date And wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value <= (Date + 7) Then
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("Agreement planned W" ).Cells(k, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value
k = k + 1
End If
i = i + 1
Wend
End Sub
Sub CSF()
Dim i As Integer
Dim j As Integer
i = 4
j = 3
While (wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value <> "" )
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value = "CSF not fulfilled" And wbSource.Worksheets("QG_Tracker" ).Cells(i, 16).Value = " Not yet happened " Then
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 8).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 30).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 9).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 10).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 31).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 11).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 32).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 12).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 33).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 13).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 34).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 14).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 35).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 15).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 36).Value
j = j + 1
End If
If wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value = "" And wbSource.Worksheets("QG_Tracker" ).Cells(i, 21).Value = "CSF not fulfilled" And wbSource.Worksheets("QG_Tracker" ).Cells(i, 16).Value = "In progress" Then
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 1).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 2).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 2).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 3).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 3).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 5).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 4).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 7).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 5).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 8).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 6).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 11).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 7).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 13).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 8).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 30).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 9).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 29).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 10).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 31).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 11).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 32).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 12).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 33).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 13).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 34).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 14).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 35).Value
wbTarget.Worksheets("CSF not fulfilled" ).Cells(j, 15).Value = wbSource.Worksheets("QG_Tracker" ).Cells(i, 36).Value
j = j + 1
End If
i = i + 1
Wend
End Sub
merci de votre aide,
Bonne soirée
