Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Validation list to copy from another Workbook
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Validation list to copy from another Workbook
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Validation list to copy from another Workbook
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste Validation List Error | Excel Discussion (Misc queries) | |||
Validation List on a shared workbook | Excel Discussion (Misc queries) | |||
Data Validation for list in a different workbook | Excel Discussion (Misc queries) | |||
Copy workbook- Validation function | Excel Worksheet Functions | |||
Data Validation - using a list from another workbook | Excel Discussion (Misc queries) |