In your code, there are references to Sheet2 and activesheet.

Option Explicit
Public Sub Tarheel()
Dim srcRng As Range
Dim destRng As Range
Dim i As Long
Dim Lrow As Long
If ActiveSheet.Range("b6").Value = "" Then
MsgBox "No records to be moved to the other sheet", _
vbExclamation, "Sorry"
Else
Lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Set srcRng = ActiveSheet.Range("B6:W" & Lrow)
Set destRng = Sheets("sheet2").Cells(Rows.Count, "B").End(xlUp)(2)
srcRng.Copy Destination:=destRng
srcRng.ClearContents
With Worksheets("sheet2")
For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B").Value < "" Then
.Cells(i, "A").Value = i - 1
End If
Next i
End With
MsgBox "Records were successfully moved", vbInformation, "Done"
End If
End Sub

Are they pointing to the correct sheets for you?

Your code worked ok for me--although, that doesn't mean it does what you want.



LoveCandle wrote:

I am using the same code mentioned above,

Code:
--------------------
Option Explicit
Public Sub Tarheel()
Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
If ActiveSheet.Range("b6").Value = "" Then
MsgBox "No records to be moved to the other sheet", _
vbExclamation, "Sorry"
Else
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
Set srcRng = ActiveSheet.Range("B6:W" & Lrow)
Set destRng = Sheets("sheet2"). _
Cells(Rows.Count, "B").End(xlUp)(2)
srcRng.Copy Destination:=destRng
srcRng.ClearContents
With Worksheets("sheet2")
For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
If .Cells(i, "B").Value < "" Then
.Cells(i, "A").Value = i - 1
End If
Next i
End With
MsgBox "Records were successfully moved", vbInformation, "Done"
End If
End Sub
--------------------

--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=487729

--

Dave Peterson