J'ai donc besoin d'une condition "Si B2 contient projetA on me copie les données de la feuille A, si B2 contient projetB on me copie les données de la feuille B" mais je ne parvient pas à placer la condition correctement, ma liste déroulante est pourtant bien créée.Sans nous montrer ton code.. il nous sera difficile de te répondre et de te dire ce qui ne va pas dans ton code....
Sub test() Dim desti As String Dim feuilSource As Worksheet Dim feuilCible As Worksheet Set feuilCible = Sheets(3) '-------------------------------------------- ' On regarde ce que vaut B2: '-------------------------------------------- desti = Sheets(3).Range("B2").Value Select Case desti Case "projetA" Set feuilSource = Sheets(1) Case "projetB" Set feuilSource = Sheets(2) Case Else 'ca vaut autre chose... Exit Sub End Select '-------------------------------------------- ' ICI le code pour récuperer les valeurs.. '-------------------------------------------- End Sub
Sub copier() 'sélection des conditions Sheets("3").Activate If Range("B2").Value Like "*PROJETA*" Then 'Activation de la feuille A Sheets("A").Activate 'Copie Sheets("A").Select Range("BilanFA").Select selection.Copy Sheets("3").Select Range("Destination").Select ActiveSheet.Paste selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Either If Range("B2").Value Like "*PROJETB*" Then .... End If End Sub
Sub copier() 'sélection des conditions Dim Fsource As Worksheet Dim Fdesti As Worksheet Dim rngDesti As Range Dim rngToCop As Range Set Fdesti = Sheets(3) Set rngDesti = Range("Destination") 'On défini les Variables en fonction de B2 If Fdesti.Range("B2").Value Like "*PROJETA*" Then Set Fsource = Sheets("A") Set rngToCop = Range("BilanFA") ElseIf Range("B2").Value Like "*PROJETB*" Then Set Fsource = Sheets("B") Set rngToCop = Range("BilanFB") End If 'Lancement de la copie Copi = CopieFromSheet(Fsource, Fdesti, rngToCop, rngDesti) End Sub Function CopieFromSheet(ShSource As Worksheet, ShDesi As Worksheet, rngTocopy As Range, rngDesi As Range) 'Activation de la feuille ShDesi.Activate 'Copie ShDesi.Select rngTocopy.Select Selection.Copy ShSource.Select rngDesi.Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Function
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub action()
'conditions
Dim Fsource As Worksheet
Dim Fdesti As Worksheet
Dim rngDesti As Range
Dim rngToCopy As Range
Set Fdesti = Sheets("Synthese")
Set rngDesti = Range("Destination")
'b2
If Fdesti.Range("B2").Value Like "*FA*" Then
Set Fsource = Sheets("FA")
Set rngToCopy = Range("BilanFA")
ElseIf Range("B2").Value Like "*EFA*" Then
Set Fsource = Sheets("EFA")
Set rngToCopy = Range("BilanEFA")
End If
'copie
Copi = CopieFromSheet(Fsource, Fdesti, rngToCopy, rngDesti)
End Sub
Function CopieFromSheet(ShSource As Worksheet, ShDesi As Worksheet, rngToCopy As Range, rngDesti As Range)
'Activation de la feuille
ShDesi.Activate
'Copie
ShDesi.Select
rngToCopy.Select
Selection.Copy
ShSource.Select
rngDesti.Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Function
j'obtiens le message "la méthode sélect de la classe range a échouéSur quelle ligne de code te mets il cette erreur ?
If Fdesti.Range("B2").Value Like "*FA*" Then