VBA: Paste in a range



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"

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question