Copier - Coller par VBA

Le 28/05/2008 à 10:56

Quelqu 'un a t'il une idée , pour transverse ca fonctionne mais pour gauche et sigmoide ca ne fonctionne pas!!!!

les cellule ne se copie pas en c18 et en d18

merci d'avance pour votre aide inestimable!!!

greg954

ps suis debutant !:!!

Sub copieindexmoteur()

'transverse'

If Sheets("Calculs").Range("I21").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I21").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

'gauche'

ElseIf Sheets("Calculs").Range("I22").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I22").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("C18").Select

ActiveSheet.Paste

'sigmoide'

ElseIf Sheets("Calculs").Range("I23").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

End If

End Sub

Edit Dan : modification du sujet

Le 28/05/2008 à 11:37

Tout d'abord: BONJOUR,,

essaies ce code:

Sub test()
moteur = Sheets("Calculs").Range("I21").Value
Select Case moteur
Case Is = "V1"
Range("F2:F31").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V2"
Range("F33:F62").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V3"
Range("F64:F93").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V4"
Range("F95:F124").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V5"
Range("F126:F155").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V6"
Range("F157:F186").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V7"
Range("F188:F217").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste

Case Is = "V8"
Range("F219:F248").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("B18").Select
ActiveSheet.Paste
End Select

gauche = Sheets("Calculs").Range("I22").Value
Select Case gauche

Case Is = "V1"
Range("F2:F31").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V2"
Range("F33:F62").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V3"
Range("F64:F93").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V4"
Range("F95:F124").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V5"
Range("F126:F155").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V6"
Range("F157:F186").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V7"
Range("F188:F217").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

Case Is = "V8"
Range("F219:F248").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("C18").Select
ActiveSheet.Paste

End Select

sigmoide = Sheets("Calculs").Range("I23").Value
Select Case sigmoide

Case Is = "V1"
Range("F2:F31").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V2"
Range("F33:F62").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V3"
Range("F64:F93").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V4"
Range("F95:F124").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V5"
Range("F126:F155").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V6"
Range("F157:F186").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V7"
Range("F188:F217").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

Case Is = "V8"
Range("F219:F248").Select
Selection.Copy
Sheets("Tableaux patient").Select
Range("D18").Select
ActiveSheet.Paste

End Select
End Sub

C'est normal que gauche et sigmoide ne fonctionnent pas dans le tien, puisqu'il trouve déjà une valeur dans transverse... il arrête donc la procédure...

J'attends ta réponse,

Souri84

Le 28/05/2008 à 12:12

Bonjour Souris 84 ( desolé pour le bonjour mais j'en suis au 3 eme poste deouis ce matin

et dans la précipitation je n'ai pas redit bonjour

en tous cas MERCI BEAUCOUP de m'avoir aider ( ca fait plaisir lol)

ca bloque tjs

Mais j'ai bricoler ca et ca marche Attention c'est de la bidouiille mais ca marche maintenant je vais essayer de bricoller t'on code qui me parrait beaucoup plus cllair que ma bidouille

merci encore

voici le code (bidouiille qui fonctionne)

encore merci

greg954

Sub transverse()

If Sheets("Calculs").Range("I21").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I21").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("B18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

End If

End Sub

Sub gauche()

If Sheets("Calculs").Range("I22").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

ElseIf Sheets("Calculs").Range("I22").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("c18").Select

ActiveSheet.Paste

Sheets("Calculs").Select

Application.CutCopyMode = False

Range("I18").Select

End If

End Sub

Sub sigmoide()

If Sheets("Calculs").Range("I23").Value = "V1" Then

Range("F2:F31").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V2" Then

Range("F33:F62").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V3" Then

Range("F64:F93").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V4" Then

Range("F95:F124").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V5" Then

Range("F126:F155").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V6" Then

Range("F157:F186").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V7" Then

Range("F188:F217").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

ElseIf Sheets("Calculs").Range("I23").Value = "V8" Then

Range("F219:F248").Select

Selection.Copy

Sheets("Tableaux patient").Select

Range("D18").Select

ActiveSheet.Paste

End If

End Sub

Rechercher des sujets similaires à "copier coller vba"