Excel vba copy worksheet from one workbook to another

[siCIVIC1986]

Hi all

I am currently trying to copy a tab from one workbook ("List.xls") to another ("IGEN_QC.xls") but I keep getting an error which I hoped somebody would be able to take a quick glance at my code to see if there is anything immediately wrong with it.

Error:
I keep getting an error that says

Method 'Copy' of object '_Worksheet' failed

Sub copydata()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String
On Error GoTo ErrHandl

' check if the file is open
ret = Isworkbookopen("List.xls")
If ret = False Then
' open file
Set wkbSource = Workbooks.Open("List.xls")
Else
' Just make it active
 Set wkbSource = Workbooks("List.xls")
 End If

' check if the file is open

ret = Isworkbookopen("IGEN_QC.xls")
If ret = False Then
' open file
Set wkbDest = Workbooks.Open("IGEN_QC.xls")
Else
' Just make it active
 Set wkbDest = Workbooks("IGEN_QC.xls")

End If

' perform copy
Set shttocopy = wkbSource.Sheets("List")
'shttocopy.Copy wkbDest.Sheets(2)
shttocopy.Copy wkbDest.Sheets

ErrHandl:
MsgBox Err.Description
Err.Clear

End Sub

Function Isworkbookopen(filename As String)
Dim ff As Long, ErrNo As Long
Dim wkb As Workbook
Dim nam As String

wbname = filename
On Error Resume Next

ff = FreeFile()
Open filename For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: Isworkbookopen = False
Case 70: Isworkbookopen = True
Case Else: Error ErrNo
End Select

End Function

Thanks in advance

 

[DavidAtWork]

using statements like
Set wkbDest = Workbooks("IGEN_QC.xls")
doesn't make that workbook the active workbook, it just sets a value for the workbook object wkbDest, to make that workbook active you need
wkbDest.Activate
once you done that you can refer to that workbook to execute a command.
With the error you're getting you could try this approach:

wkbSource.Activate
Application.Goto ActiveWorkbook.Worksheets("List").Range("A1:HH65000")
Selection.Copy
wkbDest.Activate
Application.Goto ActiveWorkbook.Worksheets(2).Range("A1")
ActiveSheet.Paste

Where I've stated a range of "A1:HH65000", choose a range for the List sheet that will include all the cells you want to copy

David

 

[siCIVIC1986]

Thanks David for your reply, I have literally this minute just managed to fix the issue:

The problem was that for some unknown reason the destination workbook was protected in some way so I could not add another worksheet to it! I converted this from a .xls to a .xlsm macro enabled excel file and then this seemed to work with no problems.