It would be helpful to know where it failed. I've tweaked your code to see
if it helps.

Option Explicit

Sub Test()
Dim i As Long
Dim myWS2 As Excel.Worksheet
Dim myWS1 As Excel.Worksheet
Dim myRow As Long
Dim myCell As Excel.Range

On Error Resume Next
Set myWS1 = ThisWorkbook.Worksheets("Sheet1")
Set myWS2 = ThisWorkbook.Worksheets("Sheet2")
On Error GoTo 0

If myWS1 Is Nothing Then
MsgBox ("Worksheet 'Sheet1' does not exist in this workbook.")
Exit Sub
End If

If myWS2 Is Nothing Then
MsgBox ("Worksheet 'Sheet2' does not exist in this workbook.")
Exit Sub
End If

For i = 0 To 5
Set myCell = myWS1.Cells(2 + i, 11)
Debug.Print myCell.Address
If myCell = 1 Then
Set myCell = myCell.Offset(0, 1)
If IsEmpty(myCell) Then
MsgBox ("Range 'myCell' is empty. myRow will not be calculated.")
Exit Sub
End If
myRow = myCell.Value
'MsgBox myrow
myCell.Copy Destination:=myWS2.Range("f" & myRow)
End If
Next i
End Sub


--
HTH,

Barb Reinhardt



"Karti" wrote:

Hi,
I am using the following code to paste the value of the given selected cell
to another sheet with the following VBA function. I have used a function to
get the row number for the given criteria in column "J".

For i = 0 To 5
If Cells(2 + i, 11) = 1 Then
myrow = Sheets("Sheet1").Cells(2 + i, 12).value
'MsgBox myrow
ThisWorkbook.Sheets("Sheet1").Cells(2 + i, 11).Copy
ThisWorkbook.Sheets("Sheet2").Range("f" & myrow).Paste
End If
Next i

While execution I am getting the following error.
Run time method '438':
Object doesn't support this property or method.

Please let me know where I am doing wrong.


.