Solved: Copy Paste from Dropdown

[Nicolaf]

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:

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

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?



Nic

[Simon Lloyd]

Not tested but try this[VBA]Workbooks.Open Filename:= _
"C:\WINDOWS\Desktop\BookFilter.xls"
Application.Run "BLPLinkReset"
Application.Run "ConnectChartEvents"
Selection.AutoFilter Field:=1, Criteria1:= Range("A1").Value
Range("C8" & Range("D" & rows.count).end(xlup).row).Copy
ActiveWindow.Close
Application.Run "BLPLinkReset"
Range("A4").Select
ActiveSheet.Paste
Range("A8").Select[/VBA]

[Nicolaf]

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.)
PeachesPearsKiwisApplesPearsCherriesGrapesApplesStrawberriesPlumsPearsRaspberriesPeaches

I also attach workbook BookFilter.xls.

Let me know if you need extra information.


Thanks,
Nix