VBA: Paste in a range
 

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.

VBA: Paste in a range
 

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


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.

--

Dave Peterson

VBA: Paste in a range
 

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.


.

VBA: Paste in a range
 

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" wrote in message
...
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


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.

--

Dave Peterson

VBA: Paste in a range
 

Maybe to allow the formats to migrate?

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



Rick Rothstein wrote:

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" wrote in message
...
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


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.

--

Dave Peterson

--

Dave Peterson

VBA: Paste in a range
 

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

VBA: Paste in a range
 

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"

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Maybe to allow the formats to migrate?

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



Rick Rothstein wrote:

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" wrote in message
...
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


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.

--

Dave Peterson

--

Dave Peterson