Copy Row based on Drop-down list results 1

[kaprmg]

Help someone.

I have 2 sheets in Excel 2002. Sheet 1 has a data validation on each cell from A7 to A41 that allows me to look at the values of A6 to A400 on Sheet 2. With the whatever I choose from the drop down list, I would like to paste the adjoining 6 cells from sheet 2 to sheet 1 (including cell formats and values). What code do I need to use? What sort of VBA code and what sort of Macro would you recommend to solve this. I need to take into account the user potentially changing the drop down list choice.

Thanks in advance,
kaprmg

 

[TonyJollans]

Hi kaprmg,

Two possible ways I can think of to populate your cells are (a) lookup formulae in the adjoining cells on Sheet1 or (b) code in the worksheet_Change event. But, before saying any more, can you tell me how you have got your validation set up to reference Sheet2 because I didn't think Validation could reference other Sheets.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kaprmg]

Hi Tony,

Thank you so much for your reply. In answer to your question, I named the cells in sheet 2 as "product" by highlighting the cells, and inserting a name. I then created a validation with drop down list referencing "=product" in the source area to keep it from getting confusing. This way, if I ever assign any more rows to sheet 2, I will just have to change the named area range.

I don't know if lookup is the right way to go. I created a master table in sheet 2 that has preconfigured cell formats adjoining the product codes on the same row (including special shading and borders). The final cell in the row is a price. I would like to be able to do several things here. In sheet 1, the validation drop down list result should spark a lookup of the adjoining cells cells on sheet 2 and automatically copy them into the adjoining cells on sheet 1. Next, be able to change the drop down list result again. I have other things on the page, so I don't want to apply this to the entire worksheet, just on a few preassigned cells.

Any help would be appreciated. I am still so new to using VBA. It is really cool stuff.

Regards,
kaprmg

 

[TonyJollans]

Hi kaprmg,

Thanks for the answer. Of course, Excel is happy with a name referring to another sheet but not with a direct reference.

Before going any further can I ask a couple of questions ..

1. Do you want to copy formatting as well as cell contents? If so you will definitely need code.

2. Reading between the lines you suggest you want this to be a one-time action. If the value selected from the dropdown changes do you want the cells beside it change or stay the same?

A sample formula, in B7, say, would be [blue][tt]=VLOOKUP(A7,OFFSET(product,0,0,,2),2,FALSE)[/tt][/blue] - but see question 1, above.

Sample code might be ..

[blue]Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7")) Is Nothing Then
        Range("B7") = ThisWorkbook.Names("product").RefersToRange.Find(Range("A7")).Offset(0, 1)
    End If
End Sub[/blue]

but depending on your answers above this will need changing.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kaprmg]

Hi Tony,

I definitely want to copy the formatting as well as cell contents. As I understand it, VLOOKUP will only work with cell values.

Thanks,
kaprmg

 

[kaprmg]

One other thing. I will want to be able to change what is in cell A7 at anytime and have it repeat the copy of what is on Sheet 2.

Thanks,
kaprmg

 

[TonyJollans]

Hi kaprmg,

As you're new to VBa, some explanation.

You need to write code which will run whenever any change is made to one of your cells. The way to do this is by creating a special routine which is triggered by Excel whenever any cell on the sheet changes. Within this routine you can check which cell(s) have changed and if your chosen cells are involved then you run whatever you want to.

The special routine is called Worksheet_Change and must go in the code module belonging to the sheet. In the VB Editor, probably on the left hand side, is the Project Explorer (if you can't see it, press Ctrl+R to bring it up). Within the project explorer you will see an entry for your workbook and under it, a heading: "Microsoft Excel Objects"; under this you will see a list of all the Sheets in your workbook. Double click on the sheet you want to change (Sheet1). This will bring the sheet's code module (probably blank) into focus in the main editor window.

At the top of the main window are two dropdowns. From the one on the left select Worksheet - this will generate some code in the window which you do not want at the moment so you can delete it. Then from the dropdown on the right select Change - this will generate a procedure which will run every time anything on he worksheet changes, and looks like this:

[blue]Private Sub Worksheet_Change(ByVal Target As Range)

End Sub[/blue]

Inside this procedure paste the following code:

[blue]Dim Source As Range
Dim SingleCell As Range

For Each SingleCell In Intersect(Target, Range("A7:A41"))

    ThisWorkbook.Names("product").RefersToRange.Find(SingleCell.Value).Offset(0, 1).Resize(1, 6).Copy
    Application.EnableEvents = False
    ActiveSheet.Paste SingleCell.Offset(0, 1)
    Application.EnableEvents = True
    
Next
    
Application.CutCopyMode = False[/blue]

That's it! What this code does is check each of your cells (range a7:a41) which has changed and looks up the value on sheet2 - it then just copies the six columns to the right including formats.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kaprmg]

Tony,

You are awesome. That worked like a charm. What do you do if you wish to change the adjacent cells at a later time. Will this requires error handling?

Thanks for all of your help.

kaprmg

 

[TonyJollans]

Hi kaprmg,

Sorry, not quite sure what you are asking. Which 'adjacent' cells? The code only does something if a cell in the range a7:a41 is changed - other cells can be changed as you wish afterwards, with the proviso that cell B7, say, will be overwritten when you change cell A7 regardless of what is in it or when it was put there.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kaprmg]

Tony,

Well, I keep getting runtime error 424 when I tested changing adjacent cells. I also tested areas above and below the cell range A7:A41 and get the same error. The code that you gave did exactly what was needed, but I have to continually push end on the error pop-up. When I try to debug, it lights up on the line:

For Each SingleCell In Intersect(Target, Range("A7:A41"))

Thanks,
kaprmg

 

[TonyJollans]

My apologies, kaprmg,

The routine needs a check to ensure there is a changed cell in the range before it can be run. Add an extra line ..

[blue][green]:
:
[/green]
Dim SingleCell As Range

[red]If Intersect(Target, Range("A7:A41")) Is Nothing Then Exit Sub[/red]

For Each SingleCell In Intersect(Target, Range("A7:A41"))

[green]:
:[/green][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[kaprmg]

Thanks Tony,

It worked like a charm.

kaprmg