Copying a section if item from a drop down list is selected

[stagiaireSTG]

Hi, I know it's very basic but I've been stuck on this for about an hour and can't figure out why it's not working.

I want the code to copy a section to another section (AI2 to AI10) if a certain item from the drop down list (positionned at B3) is selected.

Here's my code:




Sub Choix()




If Range("B3") = "Eau" Then
Range("AK2:AK3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste

ElseIf Range("B3") = "Ciment" Then
Range("AL2:AL4").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste


ElseIf Range("B3") = "Agrégats" Then
Range("AM2:AM3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste

ElseIf Range("B3") = "Sable" Then
Range("AN2:AN3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste

ElseIf Range("B3") = "Béton" Then
Range("AO2:AO3").Select
Selection.Copy
Range("AI2:AI10").Select
ActiveSheet.Paste
End If


End Sub


Thanks

[offthelip]

put this in the worksheet change event for the sheet, this will call your subroutine when the worksheet is changed



Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B3:B3")) Is Nothing) Then
Call choix
End If


End Sub

[Bob Phillips]

You can also simplify choix


Sub choix()
Select Case Range("B3").Value

Case "Eau": Range("AK2:AK3").Copy Range("AI2:AI10")

Case "Ciment": Range("AL2:AL4").Copy Range("AI2:AI10")

Case "Agrégats": Range("AM2:AM3").Copy Range("AI2:AI10")

Case "Sable": Range("AN2:AN3").Copy Range("AI2:AI10")

Case "Béton": Range("AO2:AO3").Copy Range("AI2:AI10")
End Select
End Sub

[mdmackillop]

You are copying 2 cells into a 9 cell range. I'd amend XLD's code as follows

Case "Eau": Range("AK2:AK3").Copy Range("AI2") 'etc

[Bob Phillips]

I did think of that Malcolm, but it works anyway, two cells goes into two, so I left it alone :).

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then Range("AJ2:AJ3").Offset(, Application.Match(Target, Array("Eau", "Ciment", "Agrégats", "Sable", "Béton"), 0)).Copy Range("AI2")
End Sub

[stagiaireSTG]

Thank you very much, that's what i did and it works, only problem that I have is that I have to go in the VBA editor and click run every time for the code to to it, I'd like it to do it automatically.

My problem is that since it's from a drop down list, it's just as if Excel sees nothing in the B3 case

[stagiaireSTG]

I'm trying even the most simple code :

Sub test()


If Cells(1, "AI") = "Eau" Then
Range("AK2:AK3").Copy Range("I5:I6")
End If


End Sub

This doesn't work.

Here's my first attemp

Sub Choix1_Click()




Select Case Range("AI1").Value


Case "Eau": Range("AK2:AK3").Copy Range("AI5:AI6")


Case "Ciment": Range("AL2:AL3").Copy Range("AI5:AI6")


Case "Agrégats": Range("AM2:AM3").Copy Range("AI5:AI6")


Case "Sable": Range("AN2:AN3").Copy Range("AI5:AI6")


Case "Béton": Range("AO2:AO3").Copy Range("AI5:AI6")
End Select


End Sub



Here's my second

Sub test()


If Cells(1, "AI") = "Eau" Then
Range("AK2:AK3").Copy Range("AI5:AI6")
End If


End Sub


Basically, I have a certain number of drop down list depending on one another, this is the code for the first, I only post this one, since they'll all be the same with minor changes.

I have done a data validation for the next drop down list, which refer to AI5:AI6, that's why i want to send it there, but it just doesn't work, if i select item number 1, the corresponding following section wont copy automatically in AI5:AI6

[mdmackillop]

Did you try the code in post #6?
Also have a look at this. (http://www.contextures.com/xlDataVal13.html)

[stagiaireSTG]

I've checked what you sent me, but since I have over a thousand combination, it would be freaking long to write them all... And post #6 doesn't work

[offthelip]

Where did you put the code in post #6? In order to get it to work the code must be placed in the worksheet where you have the pulldown list.
(Not in any of the modules). Under the VBAproject, right click the sheet and select view code, copy the code into the window that opens up.

[stagiaireSTG]

It says there is a Run-time error '13': Type Mismatch, could you email me so i could send you the whole document? stagiaire at bmhsystems.com

[offthelip]

Try the code in post #2 with your original sub choix and see if it does anything.
You can post your workbook to this site. Click "Go Advanced" and then manage attachments

[stagiaireSTG]

No it doesn't... I really have no idea what's not working but it's pretty annoying...

[offthelip]

it is a simple problem your list of materiaux in AG has a space in front of each of the strings , while the matching strings you are looking for don't

"Ciment" is not equal to " Ciment"

[mdmackillop]

Something like this (error noted above corrected)

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then Range("AJ2:AJ3").Offset(, Application.Match(trim(Target), Array("Eau", "Ciment", "Agrégats", "Sable", "Béton"), 0)).Copy Range("AI2")
End Sub