View Full Version : Easy Copy paste - Reconciliation macro

03-21-2013, 01:45 AM
Hi I'm still a newbie in this business so please be patient. 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 invoices (ie Batch total ONLY without the indidual lines per invoice). 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; Balances detail worksheet. I want to lookup in Balances worksheet, column A with invoice numbers, then match to the detailed Invoices worksheet also in column A. If invoices are found matching i want then to copy the whole rows to the created Balances detail worksheet.

03-21-2013, 03:52 AM
Managed to knock off some code by reverse engineering. Still not achieving what I want to. 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=Balances Detail. Then look up values in WS2 against WS1. Where a match is found I want to copy row(s) from WS1 to WS3 that is all

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 CpyRng = Union(CpyRng, mFIND)
Set mFIND = .Range("A:A").FindNext(mFIND)
Loop Until mFIND.Address = mFIRST.Address
CpyRng.EntireRow.Copy Sheets("Balances Detai").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
End Sub

03-21-2013, 08:50 AM
Come on people......there surely must be someone who knows how to get this done, please help

03-22-2013, 08:46 PM
Can you attach a sample workbook with dummy data in the formats you want to use?