Dropdown menu - VBA copy values

[Ransborg]

Dear All,

In excel 2003 I have an excel file with two sheets:

Sheet 1 contains an input sheet for my users - this sheet is called: "Resource pool", I have several data in the sheet. In column E I have dropdown menues for each cell - The user can determine diffent allocation types. In columns G to BC is the input fields.

Sheet 2 (called AllocationTypes) contains default input values for each allocation types. In Column A is stated the same allocation types as in sheet1 - and the default data is in column D to AZ.

What I would like to happen within VBA is, that based on a change in a dropdown menu in sheet 1, it will copy the default values from the AllocationTypes sheet in the input sheet..

Afterwards the user should be able to adjust the default values in sheet 1; - if they would like to do that.

Can you please advice me with suchs a macro

Thank in advance

Kind regards
Ransborg

 

[Richard Schollar]

Hello

Do you want all the values in D:AZ of Sheet2 in the relevant row copied across to Sheet 1 in G:BC or only one/some of them?

 

[Ransborg]

Hi,

thanks for the promt reply - just copy in across - the input range is equal to the out range in columns

Kind regards

 

[Ransborg]

If it helps, I can distribute the excel sheet

 

[Richard Schollar]

Right-click on the tab name of Sheet 1 and select View Code. Paste the following into the code module that will open:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
    Application.EnableEvents = False
    Set r = Sheets("AllocationTypes").Range("A:A").Find(What:=Target.Value)
    If Not r Is Nothing Then
        r.Offset(0, 3).Resize(1, 49).Copy Destination:=Target.Offset(0, 2)
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[Ransborg]

Thank you very must, just one more question - I get this error message:

Ambigeous name detected: Worksheet_Change

How do I manage this?

Kind regards

 

[Richard Schollar]

You must have two procedures in the sheet module with that name - do you already have Worksheet_Change event code in the module where I asked you to paste that code to? If so, you will need to modify the existing code (in which case, please post the existing code).

 

[Ransborg]

the existing code is here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Descrip As String
Dim thisrow As String
Dim r As Double
Dim c As Double

c = Selection.Columns.Count
r = Selection.Rows.Count
thisrow = Target.row

If c > 1 Or r > 1 Or Target.column <> 8 Or Target.Cells.Count <> 1 Then
Exit Sub
End If
If IsError(Application.VLookup(Range("H" & thisrow).Value, Worksheets("Projects").Range("$B$3:$G$2000"), 6, False)) And Target.column = 8 Then
Range("I" & thisrow).Value = ""
Exit Sub
End If
If Target.Cells.Count = 1 Then
Descrip = Application.VLookup(Range("H" & thisrow).Value, Worksheets("Projects").Range("$B$3:$G$2000"), 6, False)
Range("I" & thisrow).Value = Descrip
End If

End Sub

Thanks a lot - you really saved me for pulling out my hairs

 

[Richard Schollar]

Did you write this code? What is it meant to be doing?

 

[Ransborg]

sorry sorry my mistakes . I put the code in the wrong sheet

Thank you very much for your help