runtime error 1004 pastespecial method of range class failed

[chichaspowa]

Hey guys wondering if u can help me out on a piece of code here.

The idea is saving 2 spreadsheets of my workbook to another file with the same spreadsheets. Whenever i run the code it gives me the 1004 pastespecial method error. Gonna paste the code so you can see.

Thanks for your help in advance.

Private Sub actserv_Click()
Dim wbk As Workbook
Application.ScreenUpdating = False
strsecondfile = "C:\Documents and Settings\Utilizador\Os meus documentos\program\armz servidor.xlsm"
With Sheets("Stock")
    .Cells.Copy
End With
Set wbk = Workbooks.Open(strsecondfile)
    With wbk.Sheets("Stock")
    .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        wbk.Save
        wbk.Close
    End With
With Sheets("Registos")
    .Cells.Copy
End With
Set wbk = Workbooks.Open(strsecondfile)
    With wbk.Sheets("Registos")
    .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        wbk.Save
        wbk.Close
    End With

Application.ScreenUpdating = True
MsgBox ("Actualizado com êxito")

End Sub

 

[VoG]

Try

Private Sub actserv_Click()
Dim wbk As Workbook
Application.ScreenUpdating = False
strsecondfile = "C:\Documents and Settings\Utilizador\Os meus documentos\program\armz servidor.xlsm"
Set wbk = Workbooks.Open(strsecondfile)
    With wbk.Sheets("Stock")
    ThisWorkbook.Sheets("Stock").Cells.Copy

    .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        wbk.Save
        wbk.Close
    End With
Set wbk = Workbooks.Open(strsecondfile)
    With wbk.Sheets("Registos")
    ThisWorkbook.Sheets("Registos").Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
        wbk.Save
        wbk.Close
    End With

Application.ScreenUpdating = True
MsgBox ("Actualizado com êxito")

End Sub

 

[chichaspowa]

thanks VOG... this was killing me im still very new to code.

 

[VoG]

You are welcome. The reason that your code failed was because opening a workbook clears the clipboard so there was nothing to paste. You have to open the workbook, then do the copy and paste.