PDA

View Full Version : Delete and move records between sheets



tracktor
05-21-2011, 02:51 PM
I need to move and delete records based on cell criteria of records. I have attached a sample of what I want the macro to do, with 4 sheets... 2 before the macro, and 2 after the macro. I am using Excel 2010. I have a comment in cell G12 of sheet 1. Please help. I haven't had any luck solving this. I know very little about coding, however I am excellent at copy and paste. LOL: pray2:

shrivallabha
05-22-2011, 08:05 AM
Hi tracktor,

Welcome to VBAExpress. Here is one way of doing this. Please test this after creating a backup copy (just in case, if it is not what you want out of it).

Following is the code:
Option Explicit
Public Sub UpdateStatus()
Dim i As Integer
Dim lLastRow As Long
Dim r As Range
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = lLastRow To 2 Step -1
Set r = Sheet2.Columns("F:F").Find(What:=Range("F" & i).Value2, LookAt:=xlWhole)
If Not r Is Nothing Then
If Range("F" & i).Offset(, -3).Value2 = r.Offset(, -3).Value2 Then
'************************************************************************
'Matching results on both Sheets
'Shifting data from Sheet1 to Sheet2
'************************************************************************
Sheet2.Cells(r.Row, 1).Resize(, 6).Interior.Color = vbBlue
Range("A" & i).Resize(, 6).Interior.Color = vbBlue
Range("A" & i).Resize(, 6).Copy
ActiveSheet.Paste Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
Application.CutCopyMode = False
Rows(i).Delete
ElseIf Range("F" & i).Offset(, -3).Value2 > r.Offset(, -3).Value2 Then
'************************************************************************
'Deleting results with lower ML# from Sheet2
'Changing the row background on Sheet1 where this has occurred.
'************************************************************************
Sheet2.Rows(r.Row).Delete
Range("A" & i).Resize(, 6).Interior.Color = vbRed
End If
End If
Next i
End Sub


This you can place in a created module or in worksheet (Sheet1) code. This you can do by right-clicking on the sheet1 tab and choosing "view code" option. I have also attached the updated file.