returning value of data validation

[carlgren]

Hi,

I hope I can explain this easily!

I have a workbook with 19 sheets. The first is named MAIN. The rest are named A through R
on the MAIN sheet I have a data validation list containing the letters A through R. It's located in Range(AA5:AD5)

when a user selects a letter on the list I want to move specific columns from the selected (A through R) sheet onto the main sheet starting at Row 17.

So for example, a user selects B
I then want to copy cells from sheet B Range(B4:B103) to sheet Main Range(D17:D116) and sheet B range(E4:E103) to sheet Main(G17:G116) etc This will get done for 18 seperate columns.

my thoughts are that if I can determine which letter was selected in the validation list I should be able to simply copy the columns from one sheet to the other but I can't figure out how to determine the string value of what was selected.

any ideas would be greatly appreciated! :)

thanks
Don

[SamT]

Change Event


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 THen Exit Sub
If Intersect(Target, Range(AA5:AD5) Is Nothing Then Exit Sub
Sheets(Target).Range(B4:B103).Copy Sheets("Main").Range("D17")
Sheets(Target).Range("E4:E103").Copy Sheets("Main").Range("G17")
.

[carlgren]

WOW! That easy!

thanks so much!!!

[carlgren]

When I put the code in I'm getting a syntax error on the line:

If Intersect(Target, Range(AA5:AD5) Is Nothing Then Exit Sub

and the next line is also highlighted in Red.

[carlgren]

I changed the code to this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("AA5:AD5")) Is Nothing Then Exit Sub
Sheets(Target).Range("B4:B103").Copy Sheets("Main").Range("D17")
Sheets(Target).Range("E4:E103").Copy Sheets("Main").Range("G17")
End Sub



to get rid of the syntax errors. but when I select a sheet in the list I'm getting a type mismatch on first line that copies the data.

I am able to manually copy and paste the data with no errors. I checked and all the cells are set to General for the format. I also changed them to Text but had the same result.

[SamT]

Try

Sheets(Target.Value).Range...

[carlgren]

My Appologies! that part is working after all.