"Copy method of Sheets class failed" VBA error

[mrlex]

Hi all, I am trying to create a formula that will copy a worksheet from my activeworkbook to a new document and save.

I have the below code, but when I run it I get an error:

"Copy method of Sheets class failed"

The sheet is not protected (which was my first guess). Any idea how to solve this?

Sub SampleMacro()
Dim FName As String
Dim Sh As Worksheet


Application.ScreenUpdating = False


FName = "MyBook" & Format(Now(), "dd-mmm-yyyy") 


If Dir("C:\" & FName & ".xlsx") = vbNullString Then
[B][COLOR=#ff0000]    Sheets(Array("Form", "Updates")).Copy [/COLOR][/B]
        For Each Sh In ActiveWorkbook.Worksheets
            With Cells
                .Copy
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteColumnWidths
            End With
            Range("A1").Select
        Next Sh
    ActiveWorkbook.SaveAs "C:\" & FName & ".xlsx", xlNormal
End If
     
Application.CutCopyMode = False
Application.ScreenUpdating = False
     
End Sub

 

[Jerry Sullivan]

Hi mrlex,

Are you trying to copy one worksheet named "Form Updates", or two worksheets named "Form" and "Updates"?

Your description mentions "... copy a worksheet...", but the code is trying to find two worksheets.