Excel Vba Copy Method of Worksheet fails

Question

I am trying to copy an excel sheet from one workbook to another without the user seeing anything but I keep getting the error 'Copy Method of Worksheet Class failed'. It seems you cant copy one sheet to another workbook unless the workbook is visible?

Thanks for any help.

Hers is the code that fails:

Private Sub CommandButton1_Click()

Dim xlapp As Excel.Application
Dim wkBook As Excel.Workbook

'Connect to Excel
Set xlapp = New Excel.Application

'Set workbook and open workbook in directory
Set wkBook = xlapp.Workbooks.Open(sFileName, xlUpdateLinksNever, ReadOnly:=False)


ThisWorkbook.Sheets("Logistics").Copy Before:=wkBook.Sheets(1)

wkBook.Close True
xlapp.Quit
Set xlapp = Nothing

End Sub

Answer 1

If you do it from Excel, you don't need calls to Excel.Application and this should work:

Private Sub CommandButton1_Click()

  Dim wkBook As Workbook

  'Set workbook and open workbook in directory
  Set wkBook = Workbooks.Open(sFileName, xlUpdateLinksNever, ReadOnly:=False)

  ThisWorkbook.Sheets("Logistics").Copy Before:=wkBook.Sheets(1)

  wkBook.Close True

End Sub

If you need something similar to your code, you need to use the current Excel Application for ThisWorkbook to point to the right workbook:

Set xlapp = ThisWorkbook.Application

If you need to do that without the user seeing the changes, you can use:

Application.ScreenUpdating = False
'Insert the rest of the code
Application.ScreenUpdating = True

Answer 2

If you want to do it in the background then you should use a controlling workbook to automate both workbooks

Better again run it as a vbs rather than vba

1. Copy this code into NotePad
2. Change the path of your source and destination workbooks to suit
3. Save it as a vbs file, ie "test.vbs"
4. Click on the vbs file to execute the copy in the background

Alternatively put this code inside a Sub in VBA and run it from the controlling workbook with both the source and destination files closed (suggest you Dimension the variables properly if you use VBA)

Dim objExcel
Dim Wb1
Dim Wb2
Dim ws
Set objExcel = CreateObject("excel.application")
On Error Resume Next
Set Wb1 = objExcel.Workbooks.Open("c:\temp\source.xlsm")
Set Wb2 = objExcel.Workbooks.Open("c:\temp\dest.xlsm")
Set ws = Wb1.Sheets("logistics")
If Not IsEmpty(ws) Then
    ws.Copy Wb2.Sheets(1)
    objExcel.DisplayAlerts = False
    Wb2.Save
    objExcel.DisplayAlerts = True
    wscript.echo "success"
Else
    wscript.echo "copy failed"
End If
Wb2.Close False
Wb1.Close False
On Error GoTo 0
objExcel.Quit
Set objExcel = Nothing

Answer 3

Try this

Sub CopyAcross()
  Workbooks("Model24.xls").Sheets("Custom").Copy Before:=Workbooks("Master.xls").Sheets(1)
End Sub

Answer 4

But I dont want the user to see the workbook opening and closing? Is there any way to copy from one workbook to another without the user seeing the workbook that is being copied to? Thanks – JC75

I need to set the workbook to visible = false, but there is no visible property for the workbook. And if i set the application window to visible = false,I get the same orginal error of 'Copy Method of Worksheet Class failed' – JC75

Another way to achieve what you want.

'~~> From within excel
Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet
    Dim sFileName As String

    sFileName = "C:\Temp.xls"

    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Logistics")

    Set wb2 = Workbooks.Open(sFileName)
    ActiveWindow.Visible = False

    wb2.Sheets.Add Before:=wb2.Sheets(1)
    ws1.Cells.Copy wb2.Sheets(1).Cells

    'Windows(wb2.Name).Visible = True
    wb2.Close SaveChanges:=True

    Set wb1 = Nothing
    Set wb2 = Nothing
End Sub