Validation List choice to copy row from another sheet

P

philobr

Active Member
Joined
Oct 17, 2005
Messages
280
Hi there,
I have two similar problems.
The first is that I need to get data copied from sheet1 to sheet2
depending on selection in a validation list.
The data is on the same row and all the same range.
So it would look something like this.
A B C
Fruit Orange Apple Pear
Veg Potato Carrot Onion
Animal Bear Cow Dog

I have the data in A in a dynamic range and validation list.
I need all the data copied from the specific rows ie
choose Animal from the validation list in Sheet2!A1 and
the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.

The second problem I think is a lot more complex.
So the same scenario above but this time its a multi select on Sheet3.
I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and
then their valid options to appear in B, C and D

I've looked through the board and there doesnt seem to be anything like this so any help will be greatly appreciated.
Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Firstly apologies for the ugly code, I ran a record macro. My VBA isnt good enough to write complete complex macros yet.
From the record I got this.
Code: 
    Application.Goto Reference:="R4C2"
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range("C5:E5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Application.Goto Reference:="R4C3"
    ActiveSheet.Paste
    Range("A1").Select
Its nearly doing what I want to solve the first problem, but the
Code: 
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
is an issue. How can I specify that its what I copied from R4C2
and pasted into the search dialog that I want to find rather than "1"?

Also how do you run a macro when an action is done.
In this case when a selection is made from the validation list in R4C2?
 
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim x1 As Double
 
If Target.Address = "$B$3" Then
x1 = WorksheetFunction.Match(Target.Value, Sheets("Sheet2").Range("A:A"), 0)
Sheets("Sheet2").Rows(1).Offset(x1 - 1, 0).Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A10")
End If
 
End Sub

B3 is your cell containing the validation list with the categories in it. Assume column A in sheet2 contains the category names.

This code will copy the relevent row from sheet2 and paste it in row 10 on sheet1 when the data validation cell is changed.

Any help?
 
Upvote 0
Hi Rob,
I cant seem to get that to work. I have 2003 that wouldnt make a difference would it?

Just so I did everything right
I pasted the code to a new Module
B3 Sheet1 has the validation list (had to use a defined name)
Column A on Sheet2 has the data

I tried changing the validation cell but nothing happened.

I tried changing the code as the sheets are the other way around.
The data is on sheet1 and the validation list on sheet2 but neither way worked.
 
Upvote 0
You need to paste this into the code area of the sheet itself, not into a module.

In the visual basic editor, on the left hand side, double-click on the sheet that contains the validation list to bring up that sheet's code area and paste the code there.
 
Upvote 0
Thats cool, thanks xlHammer

Wouldnt be able to look into the second part of the problem.

I need to be able to do a multi select from the list and do the same copy paste.
If not its cool even just that has saved so much time.
 
Upvote 0
xlhammer is it possible to copy everything from the cell to the right of the validation selection.
I tried changing the offset value, but is it the Rows(1) that needs to be changed in
Code: 
Sheets("Sheet2").Rows(1).Offset(x1 - 1, 0).Copy
 
Upvote 0
Have you considered a VLOOKUP formula rather than a macro

=VLOOKUP(Sheet2!A1,Sheet1!$A$1:$D$100,Column(),False)
 
Upvote 0
Is this what you're after?

Code: 
Sheets("Sheet2").Rows(x1).Resize(1, Rows(x1).Columns.Count - 1).Offset(0, 1).Copy

The second part of your question is getting more complicated, I think you may have to use a list box if you want multiple selections.
 
Upvote 0
Hi guys,

mikerickson, the formula is just copying the cell rather than the whole row.

xlhammer, thats exactly what I needed, thanks for this.
I had thought it would be getting very complex with multiple selects and check boxes.

Can I copy/paste and change the references for 10 rows and have 10 validation list or
would I have to have ten separate macros?

Thanks again for this
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Source Formatting from a Data Validation
2
Replies
10
Views
410
CantExcel123
C
R
  • Solved
data validation list --> same color from source?
Replies
1
Views
64
rexcel3
R
N
  • Question
Worksheet Event adding and removing Data Validation based on multiple Conditions
Replies
0
Views
305
Nathan Asius
N
M
  • Question
Index Match formula pulling from a longer list with blank row spaces or unwanted data into a shorter list without row spaces
Replies
5
Views
91
Eric W
Eric W
J
  • Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
195
juliecooper255
J

Forum statistics

Threads
1,216,744
Messages
6,132,471
Members
449,729
Latest member
davelevnt

Share this page

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