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]

Welcome to MrExcel.

Try (untested):

Sub Format()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim val As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set wb1 = Workbooks("book1.xls")
    Set wb2 = Workbooks("book2.xlsm")
    val = wb2.Worksheets("sheet1").Range("m1").Value
    Set ws2 = wb2.Worksheets("sheet1")
    For Each ws1 In wb1.Worksheets
        If ws1.Cells(2, 14) <> val Then
            ws1.Range("A2:M17").Copy
            With ws2
                .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next ws1
End Sub

 

[My Aswer Is This]

Andrew. Must a workbook be open to modify a sheet cell value? I would think it must but I'm not positive. Or can a vba script actually change a closed workbook.

 

[Andrew Poulsom]

It is possible to write to a closed workbook using ADO, but I've never tried it.

How To Use ADO with Excel Data from Visual Basic or VBA

 

[My Aswer Is This]

Thanks Andrew.

 

[febrian91]

Welcome to MrExcel.

Try (untested):

Sub Format()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim val As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set wb1 = Workbooks("book1.xls")
    Set wb2 = Workbooks("book2.xlsm")
    val = wb2.Worksheets("sheet1").Range("m1").Value
    Set ws2 = wb2.Worksheets("sheet1")
    For Each ws1 In wb1.Worksheets
        If ws1.Cells(2, 14) <> val Then
            ws1.Range("A2:M17").Copy
            With ws2
                .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    Next ws1
End Sub

Thank you very much Sir,
i've tried your formula, but it still didn't work. Excel's not copying data. what's wrong with the formula? i've just started to learn about VBA

here I enclosed the illustration:
Book 1:
A B C D
Amount Amount Amount Currency
1 1 2 4 SGD
2 2 6 9 USD
3 4 7 3 IDR

Book 2:
A B C D
Amount Amount Amount Currency Criteria <> IDR
1
2
3




I want to take the data from book 1 to book 2 except for IDR currency

 

[Andrew Poulsom]

This:

If ws1.Cells(2, 14) <> val Then

is checking N2. Isn't that what you want to do?

 

[febrian91]

Yeah you are right. We are misunderstanding. So I mean i want to copy data from book1 for all of foreign currency exept for IDR currency. So in manual it seems like filter data only for foreign currency without copying IDR currency. am i right if i use "if" formula to determine the criteria or do you have another advice? Can i get your email address, so i can discuss more about VBA with you. Thank you in advance.

 

[Andrew Poulsom]

Why don't you copy all the data then delete the IDR entries?

 

[febrian91]

Why don't you copy all the data then delete the IDR entries?

because i want to know how the basic of VBA to copy with criteria. So i'm starting from the simplest example. In more complex situation, maybe i want to use more than 1 criteria. Thank you