Dropdown menu - VBA copy values

Ransborg

New Member
Joined
Nov 16, 2009
Messages
14
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Nov 16, 2009
Messages
14
Hi,

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

Kind regards
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Code:
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

New Member
Joined
Nov 16, 2009
Messages
14
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

New Member
Joined
Nov 16, 2009
Messages
14
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
 

Ransborg

New Member
Joined
Nov 16, 2009
Messages
14
sorry sorry my mistakes . I put the code in the wrong sheet :(

Thank you very much for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,122,977
Messages
5,599,151
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top