Using Validation list to copy from another Workbook

S

stakar

Hi !!
I Have a a validation list that it will helps me to copy and paste som
particular columns from a workbook to another.
Being more specific.

I have a workbook A.
I have under the column B row 4 (B4) a list that contains e.g. th
words one, two, three etc.

What I want is.
When i choose the list item 'two' i want to be connected with a macr
that it will do the following:

It will go to the already open workbook B, it will copy the column'
range A4:A800 and then to paste special to the column B4:B800 of th
workbook A.

If i choose the list item 'one' i want to be connected with a macr
that it will do the following:
It will go to the already open workbook B, it will copy the column'
range G4:G800 and then to paste special to the column B4:B800 of th
workbook A.

Thanks in advance

Stathi
 
G

gitcypher

I've come to the same dead end of wanting to run a macro with th
in-cell drop down validation. What I ended up doing was inserting
combo box in the sheet (from the forms toolbar). You can specify th
range of cells to use in its list of values, then, assign a cell for i
to link to. This link cell just returns the position within the list o
the value you selected.
For example, if your list was {Apples, Oranges, Grapes, Lemons}, an
you selected lemons, the linked cell would have '4' in it (the 4t
value). Although returning the values has some advantages, returnin
the position is good for vlookups.
After you have your combo box, you can assign code to it that run
every time the value in it is changed. Going back to the previou
example, If you select Lemons, the linked cell (best if you assign
name to it) would have a value of 4, and you can write code that run
specific lines (or call other macros) based on the value of the linke
cell.

To do what you want, I think it may be easiest to use the 'select case
function (conceptually the same as SWITCH in c++ if you're familiar)
Here's an example.

Sub DropDown1_Change()

Select Case Range("*THE NAME OF THE LINKED CELL*").Value

Case 1 '<<if you choose the first value in the dropdown
Windows("*WORKBOOK B.xls*").Activate
Range("G4:G800").Copy
Windows("*WORKBOOK A.xls*").Activate
Range("G4").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Case 2
'Insert code, or call another function
Case 3
'Insert code, or call another function
Case 4
'Insert code, or call another function
End Select

End Sub

Hope this helps

-Gitcyphe
 
S

stakar

It doesnt work at all !!

Maybe you dont understand what i have to do.
I have created a validation list with the follow items
"One"
"Two"
"Three"
under the cell H3

When i choose an item e.g the "One" i want to trigger a macro tha
doing the following :

Windows("Workbook A.xls").Activate
Range("C4:C800").Copy
Windows("Workbook B.xls").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
-----------------------------

When i choose the second item "Two" i will trigger tha above code bu
i ll change the columns. So i ll get the following:

Windows("Workbook A.xls").Activate
Range("D4:D800").Copy
Windows("Workbook B.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Thats al
 
Top