VBAhelp, how to copy data with from worksheet A (previous month) and paste in to empty columns in worksheet B with match restrictions in the same row

[Armien7]

Hi everyone! I hope you are all having a wonderfull Christmas . I'm having huge trouble with a macro which I'm trying to build for my small business, and I hope I can get some advice from you guys.

Every month I get a overview with my account receivables in a spreadsheet. Column A:T consists of customer information (for example in cells A2:A100 I have my customers name, B2:B100 their number, etc). This format doesn't change, only the number of rows differ each month (based on how many customers I have). For each row I make a personal note in column U and V.

I'm trying to build a macro that copies and pastes my personal notes from column U and V for every row from worksheet A (previous month) to worksheet B (new month data with same format, without the notes from the previous month), if the cells in column A:E (name, phone-number, total balance, etc) match eachother, so that I have the correct personal note for the corresponding customer.

I have tried VLookups, IndexMatches and Matches, but so far I have not been able to have it work properly. Hopefully I can get some new insight from you all

Wish you all a beautiful new year!

 

[Larry Haydn]

For all items with your notes, copy the following columns to a new worksheet (name it NOTES):
* Customer Name (provided these names are unique)
* Col U
* Col V
Then for new data, you can vLookUp from the NOTES worksheet.

 

[tonyyy]

You might consider the following...

Sub AnotherKoncatenate()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant
Dim i As Long, j As Long

Set ws1 = Sheets(1) ' change to your previous month sheet
Set ws2 = Sheets(2) ' change to your current month sheet
LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
arr1 = ws1.Range("A1:V" & LastRow1)
arr2 = ws2.Range("A1:V" & LastRow2)

For i = 2 To UBound(arr1)
    For j = 2 To UBound(arr2)
        If arr1(i, 1) & arr1(i, 2) & arr1(i, 3) & arr1(i, 4) & arr1(i, 5) = _
            arr2(j, 1) & arr2(j, 2) & arr2(j, 3) & arr2(j, 4) & arr2(j, 5) Then
                arr2(j, 21) = arr1(i, 21)
                arr2(j, 22) = arr1(i, 22)
                Exit For
        End If
    Next j
Next i

ReDim arr3(1 To UBound(arr2), 1 To 2)
For i = 1 To UBound(arr2)
    arr3(i, 1) = arr2(i, 21)
    arr3(i, 2) = arr2(i, 22)
Next i
ws2.Range("U1:V" & UBound(arr2)) = arr3
End Sub

Happy Holidaze!

Tony

 

[SpillerBD]

Have you ever seen the use of Advanced Filter using the Copy To: (results) option?

 

[Armien7]

For all items with your notes, copy the following columns to a new worksheet (name it NOTES):
* Customer Name (provided these names are unique)
* Col U
* Col V
Then for new data, you can vLookUp from the NOTES worksheet.

I dind't think of that, thank you very much for the tip!

 

[Armien7]

You might consider the following...

Sub AnotherKoncatenate()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant
Dim i As Long, j As Long

Set ws1 = Sheets(1) ' change to your previous month sheet
Set ws2 = Sheets(2) ' change to your current month sheet
LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
arr1 = ws1.Range("A1:V" & LastRow1)
arr2 = ws2.Range("A1:V" & LastRow2)

For i = 2 To UBound(arr1)
    For j = 2 To UBound(arr2)
        If arr1(i, 1) & arr1(i, 2) & arr1(i, 3) & arr1(i, 4) & arr1(i, 5) = _
            arr2(j, 1) & arr2(j, 2) & arr2(j, 3) & arr2(j, 4) & arr2(j, 5) Then
                arr2(j, 21) = arr1(i, 21)
                arr2(j, 22) = arr1(i, 22)
                Exit For
        End If
    Next j
Next i

ReDim arr3(1 To UBound(arr2), 1 To 2)
For i = 1 To UBound(arr2)
    arr3(i, 1) = arr2(i, 21)
    arr3(i, 2) = arr2(i, 22)
Next i
ws2.Range("U1:V" & UBound(arr2)) = arr3
End Sub

Happy Holidaze!

Tony

The code works perfectly! Thank you so much Tony for your enlightenment, you made my day!

 

[Armien7]

Have you ever seen the use of Advanced Filter using the Copy To: (results) option?

Hmm no I haven't, thank you for the tip!

 

[tonyyy]

The code works perfectly! Thank you so much Tony for your enlightenment, you made my day!

You're very welcome, Armien7.

Please remember to mark your question as solved.

Happy New Year!