VBA: Paste in a range

[Karti]

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.

 

[Dave Peterson]

First, column J is the 10th column.

Second, you can use .pastespecial, but even simpler is to just copy|paste.

with thisworkbook
.worksheets("sheet1").cells(2+i,11).copy _
destination:=.worksheets("sheet2").range("f" & myrow)
end with

Although, I like to be consistent:

with thisworkbook
.worksheets("sheet1").cells(2+i,11).copy _
destination:=.worksheets("sheet2").cells(myrow, "f")
end with

 

[Barb Reinhardt]

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

 

[Rick Rothstein]

Since the OP just wants to copy a single value for each iteration of the
loop, why not skip copy/paste and just assign it directly...

With ThisWorkbook
.Worksheets("Sheet2").Range("F" & myrow).Value = _
Worksheets("Sheet1").Cells(2 + i, 11).Value
End With

-
Rick (MVP - Excel)

 

[Dave Peterson]

Maybe to allow the formats to migrate?

Maybe because the value is currency or a date? (use .value2 instead).

 

[Jef Gorbach]

At first look, it seems like Rick's direct assignment would be the
easiest way, so I'm guessing Karti needs formatting/etc copied as
well, however just to clarify my own (mis)understanding, but isn't
Myrow holding the VALUE of Sheet1.cells(2+i,12) instead of the ROW
number like he seems to want??

I'm thinking that line should read: myrow =
Sheets("Sheet1").Cells(2 + i, 12).row

 

[Rick Rothstein]

Perhaps you are right on that, but I based my original response on the OP's
opening statement...

"I am using the following code to paste
the value of the given selected cell"