PDA

View Full Version : Solved: Copy Paste from Dropdown



Nicolaf
09-13-2012, 08:59 AM
Hi,

I need to go into a workbook (called BookFilter.xls) select a name in a dropdown filter, copy the data and paste it into another workbook (called BookData.xls).

The code I have saved is the following:

Workbooks.Open Filename:= _
"C:\WINDOWS\Desktop\BookFilter.xls"
Application.Run "BLPLinkReset"
Application.Run "ConnectChartEvents"
Selection.AutoFilter Field:=1, Criteria1:="Spain"
Range("C8:D8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
Application.Run "BLPLinkReset"
Range("A4").Select
ActiveSheet.Paste
Range("A8").Select
End Sub


So the Criteria in the dropdown filter for my selection is Spain.

If I wanted to make the criteria equal to the text in cell A1 of BookData.xls (Spain or whatever other country we choose) how would I change my code?

:dunno :doh:

Nic

Simon Lloyd
09-13-2012, 11:12 AM
Not tested but try thisWorkbooks.Open Filename:= _
"C:\WINDOWS\Desktop\BookFilter.xls"
Application.Run "BLPLinkReset"
Application.Run "ConnectChartEvents"
Selection.AutoFilter Field:=1, Criteria1:= Range("A1").Value
Range("C8:D" & Range("D" & rows.count).end(xlup).row).Copy
ActiveWindow.Close
Application.Run "BLPLinkReset"
Range("A4").Select
ActiveSheet.Paste
Range("A8").Select

Nicolaf
09-17-2012, 10:06 AM
Hi,

When I run this macro I get as result the titles of the dropdowns so Country and Fruit but I do not get what is under our chosen country ie. Spain.

Please be advised that the data in BookFilter.xls is the following:

Column C
Country (title in cell C1)
Data in Cells below C1 (so C2, C3 etc.)
Italy FranceItalyFranceFranceFranceSpainUKUKSpainUKUKSpain

Column D
Fruit (title in cell D1)
Data in Cells below D1 (so D2, D3 etc.)
PeachesPearsKiwisApplesPearsCherriesGrapesApplesStrawberriesPlumsPearsRaspb erriesPeaches

I also attach workbook BookFilter.xls.

Let me know if you need extra information.

:doh:
Thanks,
Nix