R

#### ryguy7272

list a bit, and probably overlooked by now. I am still struggling with the

same issue, and am hoping to find a solution soon.

As I stated in the other post, I want to Compare two sheets, then copy/paste

results from an entire row on a sheet named â€˜Analysis of Interest Currentâ€™,

if the values are different in Column A. Basically, I am creating a new

sheet, named â€˜Analysis-Sheetâ€™ and I am trying to compare the values in Column

A of â€˜Analysis-Sheetâ€™ (these values came from â€˜Analysis of Interest Priorâ€™)

with the values in Column A of â€˜Analysis of Interest Currentâ€™. Then, I want

to copy and past the entire row from â€˜Analysis of Interest Currentâ€™ if the

value in Column A is not in Column A of â€˜Analysis-Sheetâ€™. The purpose of

this, is that some new accounts are added to â€˜Analysis of Interest Currentâ€™

from time to time, and I want to be able to pick up these new accounts in my

analysis. For instance, I have account number 7250-0000 in both

â€˜Analysis-Sheetâ€™ (this data comes from 'Analysis of Interest Prior') and

'Analysis of Interest Current', also, I have account number 7252-0000 in both

'Analysis of Interest Prior' and 'Analysis of Interest Current'. However, I

donâ€™t have account number 7254-0000 and I donâ€™t have account number

7255-0000; both only appear in 'Analysis of Interest Current', so I'd like to

take copy all contents from both rows from 'Analysis of Interest Current' and

place it into the appropriate place in â€˜Analysis-Sheetâ€™, in ascending order.

How can I do this?

This is what I have so far:

Sub CompareSheets1()

'Delete the sheet "Analysis" if it exist

Application.DisplayAlerts = False

On Error Resume Next

ActiveWorkbook.Worksheets("Analysis-Sheet").Delete

On Error GoTo 0

Application.DisplayAlerts = True

Dim ws1 As Worksheet, ws2 As Worksheet

Dim lngRow As Long, lngFRow As Long

Set ws1 = Worksheets("Analysis of Interest Prior")

Set ws2 = Worksheets("Analysis of Interest Current")

ws1.Copy After:=ws2

Set ws1 = ActiveSheet

ActiveSheet.Name = "Analysis-Sheet"

ws1.Columns("F:J").Clear

ws2.Range("E1:E9").Copy ws1.Range("F1:F9")

Dim wks1 As Worksheet, wks2 As Worksheet

Set wks1 = Sheets("Analysis of Interest Current")

Set wks2 = Sheets("Analysis-Sheet")

For i = iLastRow To 2 Step -1

For Each j In wks1.Range("A2:A170")

If IsError(Application.Match(Cells(i, "A").Value,

wks2.Range("A2:A170"), 0)) Then

wks1.Cells(i, "A").EntireRow.Insert

wks2.Range.EntireRow.Copy

ActiveCell.Value = wks2.Range("A2:A170").Value

End If

Next j

Next i

End Sub

However, I don't know how to tell Excel to copy from the appropriate row in

wks2.

I know this is not right:

wks2.Range.EntireRow.Copy

There's no reference for the row! How do I tell Excel which row to copy?

Also, the loop is not working right. I think I'm close to a solution. What

do I need to do to make this work?

Thanks,

Ryan--