Looping Copy and Paste values based on multiple criteria in other tab

[Vindaloo]

I am new to the forums and am teaching myself VBA. I am trying to paste cells from an input tab, send them to the appropriate tab which is the variable, Tabref. When the macro finds the correct tab, it will then match criteria in 3 separate columns, which are Abbrev, Merchant ID, and Amount. If the row in the columns matches all 3, it should paste the variables Deposit_Date and Deposit_Number into their appropriate columns. I am not able to make reference formulas to concatenate on the other tabs because there are over 100 tabs and they would slow down the workbook. Also, this would interfere with existing scripts. I am getting compiling errors such as not having a Do with a Loop.

Thank you for any help you can provide,
Oren




Sub Populate_CC_Activity()
'
' Populate_CC_Activity Macro
' Pull Bank Deposit Dates and Deposit numbers from the Activity Reports Tab and paste them into the appropriate Tab



'
Dim Tabref As String
Dim Abbrev As String
Dim Merchant_ID As String
Dim Amount As String
Dim Deposit_Date As String
Dim Deposit_Number As String
Dim i As Integer
i = 3

With Worksheets("Activity Reports")

Do Until .Cells(i, 1).Value = ""

Tabref = .Cells(i, 13).Value
Abbrev = .Cells(i, 10).Value
Merchant_ID = .Cells(i, 9).Value
Amount = .Cells(i, 5).Value
Deposit_Date = .Cells(i, 11).Value
Deposit_Number = .Cells(i, 12).Value
Dim j As Integer


Cells(i, 11).Select
Selection.Copy
Sheets(Tabref).Select
j = 5
If ActiveSheet.Cells(j, 2) = Abbrev And ActiveSheet.Cells(j, 4) = Amount And ActiveSheet.Cells(j, 5) = Merchant_ID Then
Cells(j, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Activity Reports").Select

i = i + 1

Loop

End With
End Sub







End Sub

 

[hateme28]

here is your code cleaned up a little. if you could provide an example table I could help you out a little more.

Sub populate_cc_activity()
    Dim tabref As String, abbrev, merchant_ID, amount, deposit_Date, deposit_num, i As Integer, j
    i = 3
    j = 5
    With Worksheets("activity reports")
        Do Until .Cells(i, 1).Value = ""
            tabref = .Cells(i, 13).Value
            abbrev = .Cells(i, 10).Value
            merchant_ID = .Cells(i, 9).Value
            amount = .Cells(i, 5).Value
            deposit_Date = .Cells(i, 11).Value
            Deposit_Number = .Cells(i, 12).Value
            If Sheets(tabref).Cells(j, 2) = abbrev And Sheets(tabref).Cells(j, 4) = amount And Sheets(tabref).Cells(j, 5) = merchant_ID Then
                Sheets(tabref).Cells(j, 6).Value = Sheets("activity reports").Cells(i, 11).Value
            End If
            i = i + 1
        Loop
    End With
End Sub

 

[Vindaloo]

Thanks for the cleanup. I am not sure how to attach the file, but here is parts of what I am doing. From:
[TABLE="width: 320"]
<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;"> <col width="64" style="width: 48pt;" span="3"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2958;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2218;"> <tbody>[TR]
[TD="width: 89, bgcolor: transparent"]Abrev
[/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[TD="width: 64, bgcolor: transparent"]Merchant ID[/TD]
[TD="width: 64, bgcolor: transparent"]Tabref[/TD]
[TD="width: 83, bgcolor: transparent"]Deposit Date
[/TD]
[TD="width: 62, bgcolor: transparent"]Deposit #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DISC[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]1234[/TD]
[TD="bgcolor: transparent"]Sheet2[/TD]
[TD="bgcolor: transparent, align: right"]7/15/2014[/TD]
[TD="bgcolor: transparent, align: right"]1234678[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]VISA[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]1234[/TD]
[TD="bgcolor: transparent"]Sheet3[/TD]
[TD="bgcolor: transparent, align: right"]7/16/2014[/TD]
[TD="bgcolor: transparent, align: right"]567890[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MASTERCARD[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]5678[/TD]
[TD="bgcolor: transparent"]Sheet2[/TD]
[TD="bgcolor: transparent, align: right"]7/17/2014[/TD]
[TD="bgcolor: transparent, align: right"]9101112[/TD]
[/TR]
</tbody>[/TABLE]

To sheet2 yellow:

[TABLE="width: 288"]
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Abrev[/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]Merchant ID[/TD]
[TD="width: 64, bgcolor: transparent"]Deposit Date[/TD]
[TD="width: 64, bgcolor: transparent"]Deposit #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DISC[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]1234[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MASTERCARD[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]5678[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
</tbody>[/TABLE]

To Sheet3 yellow:

[TABLE="width: 288"]
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Abrev[/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]Merchant ID[/TD]
[TD="width: 64, bgcolor: transparent"]Deposit Date[/TD]
[TD="width: 64, bgcolor: transparent"]Deposit #[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]VISA[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent, align: right"]1234[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
</tbody>[/TABLE]


VBA Code:

Sub Autopopulate()
Dim tabref As String
Dim abbrev As String
Dim merchant_ID As String
Dim amount As String
Dim deposit_Date As String
Dim deposit_num As String
Dim i As Integer
Dim j As Integer
i = 5
j = 2
With Worksheets("activity reports")
Do Until .Cells(i, 1).Value = ""
tabref = .Cells(i, 1).Value
abbrev = .Cells(i, 10).Value
merchant_ID = .Cells(i, 3).Value
amount = .Cells(i, 2).Value
deposit_Date = .Cells(i, 5).Value
Deposit_Number = .Cells(i, 6).Value
If Sheets(tabref).Cells(j, 1) = abbrev And Sheets(tabref).Cells(j, 2) = amount And Sheets(tabref).Cells(j, 3) = merchant_ID Then
Sheets(tabref).Cells(j, 5).Value = Sheets("activity reports").Cells(i, 5).Value
End If
i = i + 1
Loop
End With
End Sub

 

[Vindaloo]

Fat Fingered the script. It now works for the first row in sheet 1 and 2, but does not continue and not sure how to get both deposit date and number:

Sub Autopopulate()
Dim tabref As String
Dim abbrev As String
Dim merchant_ID As String
Dim amount As String
Dim deposit_Date As String
Dim deposit_num As String
Dim i As Integer
Dim j As Integer
i = 5
j = 2
With Worksheets("activity reports")
Do Until .Cells(i, 1).Value = ""
tabref = .Cells(i, 4).Value
abbrev = .Cells(i, 1).Value
merchant_ID = .Cells(i, 3).Value
amount = .Cells(i, 2).Value
deposit_Date = .Cells(i, 5).Value
Deposit_Number = .Cells(i, 6).Value
If Sheets(tabref).Cells(j, 1) = abbrev And Sheets(tabref).Cells(j, 2) = amount And Sheets(tabref).Cells(j, 3) = merchant_ID Then
Sheets(tabref).Cells(j, 5).Value = Sheets("activity reports").Cells(i, 5).Value

End If
i = i + 1
Loop
End With
End Sub