Runtime error 1004: paste method of worksheet class failed

[sdr333]

Hi everyone,
first time on here and this place is so helpful. first time using VBA for excel and i have learnt everything i know so far from here! alas im still stuck!

So i have a worksheet with raw data on it called "Transient testpilot" and i have a calculation worksheet called "Hot-End". basically i want to take the first row of data andcopy it into specific fields in the "Hot-End" sheet. these feilds will then be used to generate results on the Hot-End sheet which i want to copy back to my raw data sheet to make a new column. once this is done i want the next row of raw data to be used untill the data is finished. i have tried many methods but all have resulted in errors. so i recorded a macro for simple copying and pasting for teh first set of data and then applied a simple "for..Next" loop. I changed what was originally Range("cell_location) to Cells(row,i), where i is a integer variable ranging from 10 to 1070 which is the range of my raw data.

so far this is the closest i have got. but i keep getting a error:

runtime error '1004' : paste method fo worksheet class failed

and for some reason even though the paste locations have beendefined by the recording, values are being pasted in random locations. ???



Sub transient()
'
' transient Macro
'
'
Dim i As Integer
For i = 10 To 1070
Sheets("Transient testpilot").Select
Cells(4, i).Select
Selection.Copy
Sheets("Hot-End ").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("Transient testpilot").Select
Cells(7, i).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hot-End ").Select
Range("C5").Select
ActiveSheet.Paste
Sheets("Transient testpilot").Select
Cells(11, i).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hot-End ").Select
Range("B5").Select
ActiveSheet.Paste
Range("F29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transient testpilot").Select
Cells(13, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub


i need to get this working by tomorrow and i have been at it for hours. any help would be much appriciated! :D

cheers

 

[sdr333]

Sub transient()
'
' transient Macro
'
'
Dim i As Integer
For i = 10 To 1070
Sheets("Transient testpilot").Select
Cells(4, i).Select
Selection.Copy
Sheets("Hot-End ").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("Transient testpilot").Select
Cells(7, i).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hot-End ").Select
Range("C5").Select
ActiveSheet.Paste >>>>>>>>>>>>> Debug say this is the error
Sheets("Transient testpilot").Select
Cells(11, i).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hot-End ").Select
Range("B5").Select
ActiveSheet.Paste
Range("F29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Transient testpilot").Select
Cells(13, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub

 

[JMonty]

I can try to work this code out but I need to be sure where from and where to copy and paste certain values. Error 1004 usually stems from a defined object error, which means either a lack of declaration or lack of an object to refer to. In simple talk, something could be missing.

Is it alright if we get a copy of the exact cells from the exact sheets and their destination? This will help us both tremendously.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] transient()
    [color=green]'[/color]
    [color=green]' transient Macro[/color]
    [color=green]'[/color]
    [color=green]'[/color]


    [color=darkblue]Dim[/color] ws1    [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] ws2    [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] i      [color=darkblue]As[/color] [color=darkblue]Integer[/color]


    [color=darkblue]Set[/color] ws1 = Sheets("Transient testpilot")
    [color=darkblue]Set[/color] ws2 = Sheets("Hot-End ")


    [color=darkblue]For[/color] i = 10 [color=darkblue]To[/color] 1070


        ws2.Range("A5").Value = ws1.Cells(4, i).Value
        ws2.Range("C5").Value = ws1.Cells(7, i).Value
        ws2.Range("B5").Value = ws1.Cells(11, i).Value


        ws1.Cells(13, i).Value = ws2.Range("F29").Value


    [color=darkblue]Next[/color] i


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[AlphaFrog]

Also, if you are just copying values between sheets so a formula can calculate a result, you could do all that within the VBA code without copy\pasting values between the two sheets.

 

[sdr333]

thankyou soo much!!! it worked a treat! yeah i really ned to lear moer VBA. recoreded macros are really inefficient the other recoreded macro i actually got working but it took like 10 times longer to complete the task!!

 

[sdr333]

so im trying to expand this macro to be even more automated. i need this loop to repeat a variable amount of times. this is the modification to the code i have made but when i run the macro, i get no response.. no warning of error, nothing!

before i was just looping from i= 10 to 1070, now in one of the cells on the work sheet i have put a counting cell which counts the rows of data on the sheet. i need to loop that many times. the location of the cell with the row count is Worksheets("Transient testpilot").Range("D4") eg. cel D4 on worksheet "transient testpilot" contains the number 26, i want to loop i= 1to 26 for this scenario.

Sub transient()
'
' transient Macro
'
'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer


Set ws1 = Sheets("Transient testpilot")
Set ws2 = Sheets("Hot-End ")


For i = 10 To Worksheets("Transient testpilot").Range("D4").Value

ws2.Range("A5").Value = ws1.Cells(i, 4).Value
ws2.Range("C5").Value = ws1.Cells(i, 7).Value
ws2.Range("B5").Value = ws1.Cells(i, 11).Value

ws1.Cells(i, 13).Value = ws2.Range("F29").Value

Next i

End Sub

 

[sdr333]

i have also tried using ws1 as the variable to indicated the location of the cell however its teh same result. i was just trying diffeernt things to make it work.

any help would be much appriciated.

thanks in advance

 

[Andrew Poulsom]

Shouldn't you be using i in both sides of your assignment expressions rather than in only one side?

 

[AlphaFrog]

so im trying to expand this macro to be even more automated. i need this loop to repeat a variable amount of times. this is the modification to the code i have made but when i run the macro, i get no response.. no warning of error, nothing!

before i was just looping from i= 10 to 1070, now in one of the cells on the work sheet i have put a counting cell which counts the rows of data on the sheet. i need to loop that many times. the location of the cell with the row count is Worksheets("Transient testpilot").Range("D4") eg. cel D4 on worksheet "transient testpilot" contains the number 26, i want to loop i= 1to 26 for this scenario.

This should work if D4 is the cell with the count and the value (in this case) is greater than 10.

Set ws1 = Sheets("Transient testpilot")
Set ws2 = Sheets("Hot-End ")


For i = [B]10[/B] To [COLOR="#FF0000"]ws1.Range("D4").Value[/COLOR]

ws2.Range("A5").Value = ws1.Cells(i, 4).Value
ws2.Range("C5").Value = ws1.Cells(i, 7).Value
ws2.Range("B5").Value = ws1.Cells(i, 11).Value

ws1.Cells(i, 13).Value = ws2.Range("F29").Value

Next i