Copy and paste data between workbooks depending on drop-down choice

Question

Just a quick one, I have coded a piece of VBA that copies and pastes data between two workbooks. However, I would like to be able to copy specific data across rather than the entire table. So workbook "x" I would like to filter column 'L' by a choice of a drop down box in workbook "y" - field "P14".

how would I do this, so that whatever the user chooses it filters and pastes that data into workbook y.

Code below for what I've done so far:

Private Sub CommandButton1_Click()

    Dim x As Workbook
    Dim y As Workbook
    Dim p As String

    Set p = y.Worksheets("Title").Cells(14, "P").Value
    Set x = Workbooks.Open("C:\Users\name\Desktop\Project
    Autonetics\CoreData")
    'x.Worksheets("Xero").Range("L1").AutoFilter Field:=1, Criteria:="p"
    With Xero
        .AutoFilterMode = False
        With .Range("L:L")
            .AutoFilter Field:=1, Criteria:="p"
            .SpecialCells (xlCellTypeVisible)
        End With
    End With
    Set y = ThisWorkbook
    x.Worksheets("Xero").Range("A1:L100000").Copy
    Application.DisplayAlerts = False
    y.Worksheets("Costings").Range("A1").PasteSpecial

    x.Close
End Sub

Have you looked into [`Range.AutoFilter`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter)? Maybe even [`Range.AdvancedFilter`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.advancedfilter) which should be faster =) — JvdV, Jan 14 '20 at 13:58
@JvdV I have indeed but I am not sure how to use the auto filter function, but that was the way I was thinking of using. How would i program this using autofilter — Daisy, Jan 14 '20 at 13:58
Recording a macro could be helpful. There are also examples here on SO of how to use `AutoFilter`. — BigBen, Jan 14 '20 at 13:59
@BigBen Hi BigBen, not too sure how to use the autofilter function - I can use macros but do you perhaps have a link to the autofilter example on SO — Daisy, Jan 14 '20 at 14:01
You should store the value from WB Y in a variable (String or whatever) and use this variable as input criteria in Range.AutoFilter — Daghan, Jan 14 '20 at 14:02
https://stackoverflow.com/questions/11903878/autofilter-macro-then-copy-visible-data-only-and-paste-to-next-available-row — BigBen, Jan 14 '20 at 14:02
@Daghan Thank you, will try and see what I can do, I have tried doing this way but struggled. Can anyone help with some code example — Daisy, Jan 14 '20 at 14:08
@BigBen Thank you, will check now. If you have any code example that would be tailored for my code that would be great because I have looking into autofilters but cant get my head around it. — Daisy, Jan 14 '20 at 14:09
You're getting there :). Now you need to copy the visible cells - see the link I provided earlier. — BigBen, Jan 14 '20 at 14:48
I would start with `Set`-ting your `Workbook` variables first, meaning `y` is not initialized (only later on in your code). If you want to see how you could handle some `AutoFilter`, than you might find [this](https://stackoverflow.com/q/58889516/9758194) an interesting read. It includes how you can check for results first to prevent errors. — JvdV, Jan 14 '20 at 14:48
@BigBen I have edited the code above but still not working, is it possible if you could help with some code? Really can't figure it out — Daisy, Jan 14 '20 at 15:43
@JvdV I have edited the code above, is there any chance you can help out with some code? I am struggling and cant seem to get it working — Daisy, Jan 14 '20 at 15:43

JvdV · Accepted Answer · 2020-01-14T16:52:22.763

Here is something for you to work with. Personally I'm not such a On Error fan, but it would be legitimate use inside to check for a returned error when using SpecialCells.

Private Sub CommandButton1_Click()

Dim wb1 As Workbook, wb2 As Workbook
Dim sht1 As Worksheet, sht2 As Worksheet
Dim lc As Long, lr As Long
Dim rng As Range, str As String

'Set your two workbooks
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Users\name\Desktop\ProjectAutonetics\CoreData")

'Set your two worksheets
Set sht1 = wb1.Worksheets("Title")
Set sht2 = wb2.Worksheets("Xero")

'Get your criteria ready
str = sht1.Range("P14").Value

'Get your range to filter ready
With sht2
    lr = .Cells(.Rows.Count, 12).End(xlUp).Row
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(1, 1), .Cells(lr, lc))
End With

'Apply filter and act if any hits
rng.AutoFilter 12, str
If rng.SpecialCells(12).Cells.Count > rng.Rows(1).Cells.Count Then
    rng.SpecialCells(12).Copy sht1.Cells(1, 1)
End If

'Close your second workbook
wb2.Close False

End Sub

I been quite extensive in the hope you can clearly see what is going on in this code.

Good luck.

That's great to hear. Glad it's been helpfull @Daisy – JvdV Jan 14 '20 at 16:53 — JvdV, Jan 14 '20 at 16:53

Copy and paste data between workbooks depending on drop-down choice

1 Answers1