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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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?
 
Upvote 0
Hi,

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

Kind regards
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top