runtime error 1004 paste method of worksheet class failed

[Yeoman.jeremy]

Hi there.
Basically, i'm trying to copy and paste certain cells from one workbook, to another, I've looked up many other threads but couldn't find anything specifically that can help me.

Basically what happens in this excel workbook, is I have a Command button that will call this macro, to transfer certain data from the current workbook into a blank version of the workbook.

Here is my coding so far


Sub DuplicateComplex_mcr()
Dim wbk As Workbook

strFirstFile = "c:\Documents and settings\Jan\Desktop\On The Job Sheet"

Set wbk = ThisWorkbook
With wbk.Sheets("Job Sheet")
.Range("B2").Copy
End With

Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Job Sheet")
Range("b2").Select
ActiveSheet.Paste

End With

End Sub



Any help would be very much appreciated

 

[RSXchin]

On The Job Sheet.xls
or something like that. I think that's the problem

 

[Yeoman.jeremy]

On The Job Sheet.xls
or something like that. I think that's the problem

The coding actually opens the other workbook fine.
When i didnt have the "Range("b2").Select" included, it pasted perfectly fine into the other workbook, in the correct sheet, except it only pasted into cell A1

 

[RSXchin]

try this

Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Job Sheet")
     .Activate
     Range("b2").Select
     ActiveSheet.Paste
    
End With

 

[Norie]

Try this.

Sub DuplicateComplex_mcr()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

strFirstFile = "c:\Documents and settings\Jan\Desktop\On The Job Sheet"

Set wbk1 = ThisWorkbook
 
Set wbk2 = Workbooks.Open(strFirstFile)

wbk1.Sheets("Job Sheet").Range("B2").Copy

wbk2.Sheets("Job Sheet").Range("B2").PasteSpecial xlPasteValues

End Sub

 

[Yeoman.jeremy]

Try this.

Sub DuplicateComplex_mcr()
Dim wbk1 As Workbook
Dim wbk2 As Workbook

strFirstFile = "c:\Documents and settings\Jan\Desktop\On The Job Sheet"

Set wbk1 = ThisWorkbook
 
Set wbk2 = Workbooks.Open(strFirstFile)

wbk1.Sheets("Job Sheet").Range("B2").Copy

wbk2.Sheets("Job Sheet").Range("B2").PasteSpecial xlPasteValues

End Sub

PERFECT!
thank you very much, saved me many a headache! :3