copy data from other workbook with criteria

[febrian91]

Dear Master,

I wanna ask, how to copy data from another workbook with criteria. my criteria is copy the values in foreign currency exept for values in IDR currency. I have used "if" formula on my VBA formula, but it doesn't work. excel can not read my "if" formula, so excel just copy data without criteria. What's wrong with my formula? here is my formula:

Sub Format()
Dim wb1 As Workbook, wb2 As Workbook
Dim val As String
Dim i As Integer
Set wb1 = Workbooks("book1.xls")
Set wb2 = Workbooks("book2.xlsm")
val = wb2.Worksheets("sheet1").Range("m1").Value
Dim ws1 As Worksheet, ws2 As Worksheet
For Each ws1 In wb1.Worksheets
Set ws2 = wb2.Worksheets("sheet1")
If Cells(2, 14) <> val Then
wb1.Worksheets("sheet1").Range("a2:m17").Copy
wb2.Worksheets("sheet1").Range("a2:m17").PasteSpecial Paste:=xlPasteValues
End If
Next ws1

End Sub

Thank you Master

 

[Andrew Poulsom]

There's some AutoFilter code here that you may be able to adapt:

Use AutoFilter to filter and copy the results to a new worksheet

 

[febrian91]

There's some AutoFilter code here that you may be able to adapt:

Use AutoFilter to filter and copy the results to a new worksheet

Ok, thank you sir for your help. i'll try your advice.

 

[febrian91]

Sir, i wanna ask. what's VBA formula should be used if i want to add fill colour "Red colour" for false condition and "Green colour" for true condition?

 

[Andrew Poulsom]

You can set the Range's Interior.ColorIndex to 3 or 10.

 

[febrian91]

Hello. Do you think if in excel, you use SQL language to copy, filter data and do dynamic reports? It is very easy if you use add-in a-tools. You can show your wordbook and i will try do it by add-in a-tools.
Thanks.

Thank you for your advice. How to do it? can you explain it?