Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy range and paste into every 3rd cell of new range | New Users to Excel | |||
Cut and Paste range | Excel Programming | |||
Paste a named range to another range in Excel | Excel Discussion (Misc queries) | |||
Paste Small Range into Big Range | Excel Programming | |||
Range Paste in VBA | Excel Programming |