Designing a code to run reconciliation Macro

March 20, 2013 at 09:02:38
Specs: Windows XP
Sales Invoices Reconcilitaion
I have a Sales workbook which has 2 worksheets namely: Invoice & Balances
1) Worksheet Invoices has the detail of all invoices raised by the company. The invoices are run as a batch & each invoice will have multiple lines per invoice number.
2) Worksheet Balances has the summary of outstanding amounts per invoice (ie Batch total ONLY without the lines). What can happen is that an invoice might have 5 different lines but a customer might only pay for 3 different lines hence the invoice will still reflect as outstanding but only with an amount for the 2 unpaid lines; summarised under one line.
What I want to do is to create an extra tab; Reconciliation. I want to lookup in Balances worksheet, column A for invoice numbers, then match to the Invoices worksheet column A. If the lines are found matching any invoice in balances i want the to copy the whole rows to reconciliation worksheet.

See More: Designing a code to run reconciliation Macro

Report •

March 20, 2013 at 10:34:23
It would help if we could see see a small example of your worksheet(s) layout before any code can be offered. Otherwise we'd be writing code against our own "test workbooks" which may not reflect your actual layout.

Please clcik on the following line and read the instructions on how to post example data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 21, 2013 at 02:14:00
Hi and many thanks for the response. I couldn’t successfully upload my worksheets and after several attempts I’ve given up. I will try and explain what I want to achieve but if it’s not doable, do not worry. Like I said I have 2 worksheets:
WS1 = Invoices
In columns A of both there are unique identifiers. What I want to do is to create WS3=Reconciliation. Then look up values in WS2 against WS1. Where a match is found I want to copy row(s) from WS1 to WS3 that all
If it can’t be done it’s all good but thanks for replying.

Report •

March 21, 2013 at 02:53:54
My limited knowledge has taken me this far. Need help to make it work

Sub CopyAndPaste()
Dim x As String, CpyRng As Range
Dim mFIND As Range, mFIRST As Range

    With Sheets("BALANCES")
        On Error Resume Next
End With
With Sheets("INVOICES")
        Set mFIND = .Range("A:A").Find(x, LookIn:=xlValues, LookAt:=xlWhole)
        If Not mFIND Is Nothing Then
            Set CpyRng = mFIND
            Set mFIRST = mFIND

                Set CpyRng = Union(CpyRng, mFIND)
                Set mFIND = .Range("A:A").FindNext(mFIND)
            Loop Until mFIND.Address = mFIRST.Address

            CpyRng.EntireRow.Copy Sheets("RECONCILIATION").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    End With
End Sub

Report •

Related Solutions

Ask Question