I need an answer pronto to this problem. I have two spreadsheets, last month's customer statements and this months customer statements. The current month doesn't have invoice descriptions, just dates and amounts. Each sheet houses a single customer statement and has the same name in both workbooks. I need to compare each sheet with the same name and if there is a line that has a matching date and amount, copy the invoice description to the current months line. I've written the following code, but with over 100 statements it takes hours to run on my machine, and doesn't work to boot!
Code
Sub updateInvoiceDescriptions()Dim curDate As Date
Dim curBalance, nextRow, oldNextRow As Long
Dim curDesc, custNum, oldWbPath As String
Dim ws As Worksheet
Dim oldWb As Workbook
oldWbPath = Application.GetOpenFilename(FileFilter:= _
"Microsoft Excel Workbooks, *.xls; *.xlsx; *.xlsm", Title:="Select Last Months Statement File")
Application.ScreenUpdating = False
Set oldWb = Workbooks.Open(oldWbPath)
For Each ws In ActiveWorkbook.Worksheets
For nextRow = 17 To 64
custNum = ws.Name
curDate = ws.Cells(nextRow, 1).Value
curBalance = ws.Cells(nextRow, 4).Value
For oldNextRow = 17 To 64
If oldWb.Sheets(custNum).Cells(oldNextRow, 1).Value = curDate And oldWb.Sheets(custNum).Cells(oldNextRow, 4).Value = curBalance Then
ws.Cells(nextRow, 2).Value = oldWb.Sheets(custNum).Cells(oldNextRow).Value
End If
Next oldNextRow
Next nextRow
Next ws
oldWb.Close
Application.ScreenUpdating = True
End Sub
Display More