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

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
here is your code cleaned up a little. if you could provide an example table I could help you out a little more.

Code:
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
 
Upvote 0
Thanks for the cleanup. I am not sure how to attach the file, but here is parts of what I am doing. From:
Abrev
AmountMerchant IDTabrefDeposit Date
Deposit #
DISC251234Sheet27/15/20141234678
VISA20001234Sheet37/16/2014567890
MASTERCARD355678Sheet27/17/20149101112
<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> </tbody>

To sheet2 yellow:

AbrevAmountMerchant IDDeposit DateDeposit #
DISC251234
MASTERCARD355678
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

To Sheet3 yellow:

AbrevAmountMerchant IDDeposit DateDeposit #
VISA20001234
<colgroup><col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>


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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top