Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two sheets, then copy/paste if different
A short time ago I was getting help with a macro that created a new sheet,
and named it €˜Analysis-Sheet. Then, I copied everything from a sheet named €˜Analysis of Interest Prior to the €˜Analysis-Sheet. The code is below: Dim EntireRange As Long 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Analysis-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Analysis" 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 Jacob Skaria helped mw with some of the code above. Now, 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 past the entire row from €˜Analysis of Interest Current if the value in Column A is not in Column A of €˜Analysis of Interest Prior. 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 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 account number 7254-0000 and account number 7255-0000 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 under the end of the list of account numbers in 'Analysis-Sheet'. How can I do this? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two sheets, then copy/paste if different
If column A is formatted as text, you could use the InStr function to test
the other sheet for a match and if no match then copy the data over. for expl Set wks1 = Sheets("Analysis of Interest Current") Set wks2 = Sheets("Analysis-Sheet") For Each c In wks1.Range("A2:A100") If InStr(wks2.Range("A2:A500"), "7252-0000") = 0 Then 'copy/paste the data End If Next The ranges are arbitrary but should give you an idea of the method. "ryguy7272" wrote in message ... A short time ago I was getting help with a macro that created a new sheet, and named it 'Analysis-Sheet'. Then, I copied everything from a sheet named 'Analysis of Interest Prior' to the 'Analysis-Sheet'. The code is below: Dim EntireRange As Long 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Analysis-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Analysis" 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 Jacob Skaria helped mw with some of the code above. Now, 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 past the entire row from 'Analysis of Interest Current' if the value in Column A is not in Column A of 'Analysis of Interest Prior'. 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 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 account number 7254-0000 and account number 7255-0000 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 under the end of the list of account numbers in 'Analysis-Sheet'. How can I do this? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two sheets, then copy/paste if different
Thanks for the info JLGWhiz. I think it's going to be something like this:
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "JLGWhiz" wrote: If column A is formatted as text, you could use the InStr function to test the other sheet for a match and if no match then copy the data over. for expl Set wks1 = Sheets("Analysis of Interest Current") Set wks2 = Sheets("Analysis-Sheet") For Each c In wks1.Range("A2:A100") If InStr(wks2.Range("A2:A500"), "7252-0000") = 0 Then 'copy/paste the data End If Next The ranges are arbitrary but should give you an idea of the method. "ryguy7272" wrote in message ... A short time ago I was getting help with a macro that created a new sheet, and named it 'Analysis-Sheet'. Then, I copied everything from a sheet named 'Analysis of Interest Prior' to the 'Analysis-Sheet'. The code is below: Dim EntireRange As Long 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Analysis-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Analysis" 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 Jacob Skaria helped mw with some of the code above. Now, 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 past the entire row from 'Analysis of Interest Current' if the value in Column A is not in Column A of 'Analysis of Interest Prior'. 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 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 account number 7254-0000 and account number 7255-0000 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 under the end of the list of account numbers in 'Analysis-Sheet'. How can I do this? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two sheets, then copy/paste if different
Any thoughts on what I am doing wrong? Any idea on how I can get this working?
Thanks, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: Thanks for the info JLGWhiz. I think it's going to be something like this: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "JLGWhiz" wrote: If column A is formatted as text, you could use the InStr function to test the other sheet for a match and if no match then copy the data over. for expl Set wks1 = Sheets("Analysis of Interest Current") Set wks2 = Sheets("Analysis-Sheet") For Each c In wks1.Range("A2:A100") If InStr(wks2.Range("A2:A500"), "7252-0000") = 0 Then 'copy/paste the data End If Next The ranges are arbitrary but should give you an idea of the method. "ryguy7272" wrote in message ... A short time ago I was getting help with a macro that created a new sheet, and named it 'Analysis-Sheet'. Then, I copied everything from a sheet named 'Analysis of Interest Prior' to the 'Analysis-Sheet'. The code is below: Dim EntireRange As Long 'Delete the sheet "Summary-Sheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("Analysis-Sheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "Summary-Sheet" Set Basebook = ThisWorkbook Set Newsh = Basebook.Worksheets.Add Newsh.Name = "Analysis" 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 Jacob Skaria helped mw with some of the code above. Now, 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 past the entire row from 'Analysis of Interest Current' if the value in Column A is not in Column A of 'Analysis of Interest Prior'. 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 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 account number 7254-0000 and account number 7255-0000 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 under the end of the list of account numbers in 'Analysis-Sheet'. How can I do this? Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and copy/paste | Excel Worksheet Functions | |||
Compare col and match then copy and paste | Excel Discussion (Misc queries) | |||
Compare two wk sheets with common data using copy paste macro | Excel Worksheet Functions | |||
Compare two Sheets and copy value | Excel Worksheet Functions | |||
Compare,copy and paste if true | Excel Programming |