Maybe....

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

I changed this line:
with sheets2
to
With Worksheets("sheet2")
(change it to what you need)

And when you use "with/end with", you'll need leading dots on the things that
belong to that "with" object.

.cells(i,"B").value....
with the dot in front of cells, excel knows that it belongs to the object in the
previous With statement.

Without the dot, excel knows that you mean the activesheet (well, in a general
module).




LoveCandle wrote:

Thank you sooooooooo much that is really fantastic and what I need
exactly,

I have also another two codes need to be merged, I almost succeeded
merging them but the problem is that the second code which should be
working on the sheets2, it works on the active sheet instead I don't
know why!!!

First Code:

Code:
--------------------
Public Sub Tarheel()
If [B6].Value = "" Then
MsgBox "No records to be moved to the other sheet", vbExclamation, "Sorry"
Else
Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
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
MsgBox "Records were successfully moved", vbInformation, "Done"
End If
End Sub
--------------------

Second Code:

Code:
--------------------
Sub Numbering()
With sheets2
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
End Sub
--------------------

Codes after merging,

Code:
--------------------
Public Sub Tarheel()
If [B6].Value = "" Then
MsgBox "No records to be moved to the other sheet", vbExclamation, "Sorry"
Else
Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
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 sheets2
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
"Records were successfully moved", vbInformation, "Done"
End If
End Sub
--------------------

Thank you,

--
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