Activecell.Resize(,2).Copy

Hi again,

The code below works fine to delete a row.....but if row columns A an
B contain data then i want to copy the values, delete the row the
paste the new values into the new row. I have highlighted in red th
code that needs to be able to perform this operation, any hel
appreciated!

Code
-------------------
   
 Public Sub DelIndividual()
 
 Dim i%, start%, finish%
 Dim s$, f$, r$, flag As Boolean
 
 flag = False
 'check for valid cell selection
 If (ActiveCell.Column = 3) And (ActiveCell.Value <> "") Then
 start = ActiveCell.Row
 i = start
 Do
 i = i + 1
 Loop Until (Cells(i, 3).Value <> "") Or (Cells(i, 7).Value = "") And (Cells(i, 8).Value = "")
 finish = i - 1
 ActiveSheet.Rows(start).Select
 'confirm
 r = MsgBox("Do you want to permanently delete this record?", 52, "Delete")
 If r = vbYes Then
 'delete the whole record
 ActiveSheet.Unprotect Password:=Pass
 
 If ActiveCell.Offset(0, 0) <> "" And ActiveCell.Offset(0, 1) <> "" Then
 'copy data and set flag
 ActiveCell.Resize(, 2).Copy
 flag = True
 End If            
 For i = start To finish
 ActiveSheet.Rows(start).Delete
 Next
 
 If flag = True Then
 ActiveCell.Resize(, 2).PasteSpecial Paste:=xlValues
 Application.CutCopyMode = False
 End If            
 ActiveSheet.Protect Password:=Pass
 End If
 ActiveCell.Offset(0, 0).Select
 End If
 
 End Sub
 

-------------------

Thanks for the code before bob, not too sure how to encorporate i
though

In sheet1 with cell (say) B3 the active cell
and with A3 containing  the Value  123
Run:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/21/2006 by Jim May
'

'
   ActiveCell.Offset(0, -1).Range("A1").Copy
   Sheets("Sheet2").Activate
   Range("D1").Select
   ActiveSheet.Paste
   Application.CutCopyMode = False
End Sub

Sheet2, Cell D1 will now contain the   123.

HTH