markpem
03-30-2015, 06:27 AM
Hello
I currently have code below which works very well.
There are two sheets
"PageToSyncFrom" - This has the data that may contain updated cells to copy over to "PageToSyncTo" - This all works perfectly.
The spreadsheet works by matching reference numbers in Column A. What I need to happen is when a match has been done and the data is synced over to delete (or clear) the row in PageToSyncTo that it has just copied from.
So (the theory) is that the only thing that would be left on PageToSyncTo is data which does not have a reference match in ColumnA on PageToSyncFrom.
I did ask this question a while ago on another forum and I can't remember it and the person advised I should put (but it doesn't seem to work):-
Add this in place of your End If: End If
Code:
End If
Cells(s1rw, col).EntireRow.Delete Shift:=xlUp
End If
So this is my current code:-
' Sync Updates with Converted Data
Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("PageToSyncFrom").Select
With Sheets("PageToSyncTo")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
Application.StatusBar = "Updating cases.. Found: " + Str$(s1rw)
For col = 2 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With
I currently have code below which works very well.
There are two sheets
"PageToSyncFrom" - This has the data that may contain updated cells to copy over to "PageToSyncTo" - This all works perfectly.
The spreadsheet works by matching reference numbers in Column A. What I need to happen is when a match has been done and the data is synced over to delete (or clear) the row in PageToSyncTo that it has just copied from.
So (the theory) is that the only thing that would be left on PageToSyncTo is data which does not have a reference match in ColumnA on PageToSyncFrom.
I did ask this question a while ago on another forum and I can't remember it and the person advised I should put (but it doesn't seem to work):-
Add this in place of your End If: End If
Code:
End If
Cells(s1rw, col).EntireRow.Delete Shift:=xlUp
End If
So this is my current code:-
' Sync Updates with Converted Data
Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long
Cancel = True ' Ignore error messages as first column is LOCKED
Sheets("PageToSyncFrom").Select
With Sheets("PageToSyncTo")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
Application.StatusBar = "Updating cases.. Found: " + Str$(s1rw)
For col = 2 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = Format(Cells(s1rw, col).Value, "mm/dd/yyyy")
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next
End If
s2rw = s2rw + 1
Loop
.Select
End With