Why can't i get the paste command to work?

[Mat K]

I have written the following code and cant the paste command to work. I suspect that i have made a syntax error somewhere but i cant work it out. I can send the full code to anyone if that would help. Any help would be greatly appreciated. the range that i am trying to copy comes from another workbook and that is the one that is closed at the start of the code shown.

Range(Cells(8, column_copy), Cells(133, column_copy)).Select
Selection.Copy

'closing
ActiveWorkbook.Close (savechanges = False)

Workbooks(wbname1).Sheets(plant).Select


' pasting routine
Range(Cells(5, column_copy), Cells(130, column_copy)).Select

' this stupid line doesnt work!!!
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Cheers in advance, Mat

[mangesh_yadav]

There doesn't seem that anythings wrong in the code. What's the error message. Also try to just select 1 cell instead of the whole range before pasting, for instance change your following line to:
Range(Cells(5, column_copy), Cells(130, column_copy)).Select
to
Cells(5, column_copy).Select


- Mangesh

[Mat K]

the error is "PasteSpecial method of Range class failed"

I tried just selected 1 cell previously and that also didnt work. I know that there is a problem with the range at some point but i cant find it and i checked that the range sizes are the same, they are, but using the cells command rather than range should have eliminated this as the problem i would have thought.

So i am still stumped...

[mangesh_yadav]

Ok. the problem is simple. You need to reference the range and cells with its parent sheet. Change your following line:
Range(Cells(5, column_copy), Cells(130, column_copy)).Select

to
Sheets(plant).Range(Sheets(plant).Cells(5, column_copy), Sheets(plant).Cells(130, column_copy)).Select


- Mangesh

[Mat K]

Ok i tried that but it still generates the same error. Any other sugestions?

[Cutter]

Is the sheet protected?

[Mat K]

no i thought of that before. The sheet that i am copying from is one that is sent to me through a macro in that sheet, and the sheets that i am copying to are all unprotected as they just contain the data that is pasted from the emailed sheet.