Error for VBA code "ActiveSheet.Copy"

Question

I have Excel VBA code that has been running quite well up to Office 2013.  However recently l put the same code (excel worbook) on a new laptop running Office 2010 and it fails at teh "ActiveSheet.Copy" command.  the code is essentially copying and pasting special the current worksheet into a new workbook and emailing that workbook. (essentially removing the formulas so i can email the values).  What is interesting is that same codes works in 2 out of the ten worksheets in the same workbook.  only these 2 worksheets error when i run the macro.  Also interesting is the code still works with some of the older laptops running Office 2010.   I am neewbie to VBA and only use this code for this one application.

Code is below:

Sub Mail_PM_CHECK_LIST()

 Dim objSheet As Worksheet

'UnProtecting all worksheets with password

     For Each objSheet In Worksheets

         If objSheet.ProtectContents = True Then objSheet.Unprotect "VZW-ASNE"

     Next objSheet

' Works in Excel 97 through Excel 2007.

Dim FileExtStr As String

Dim FileFormatNum As Long

Dim Sourcewb As Workbook

Dim Destwb As Workbook

Dim TempFilePath As String

Dim TempFileName As String

With Application

   .ScreenUpdating = False

   .EnableEvents = False

End With

Set Sourcewb = ActiveWorkbook

' Using ActiveSheet.Copy creates a new workbook with

' the sheet and the file format is the same as the

' original workbook.

' Copy the worksheet to a new workbook.

ActiveSheet.Copy (this is where the code fails)

Set Destwb = ActiveWorkbook

' Determine the Excel version and file extension/format.

With Destwb

   If Val(Application.Version) < 12 Then

      ' You are using Excel 97-2003.

      FileExtStr = ".xls": FileFormatNum = -4143

   Else

      ' You are using Excel 2007.

      ' When you use ActiveSheet.Copy to create a workbook,

      ' you are prompted with a security dialog. If you click No

      ' in the dialog, then the name of Sourcewb is the same

      ' as Destwb and you exit the subroutine. You only see this

      ' dialog when you attempt to copy a worksheet from an .xlsm file with macros disabled.

      If Sourcewb.Name = .Name Then

         With Application

            .ScreenUpdating = True

            .EnableEvents = True

         End With

         MsgBox "Your answer is No in the security dialog."

         Exit Sub

      Else

         Select Case Sourcewb.FileFormat

            ' Code 51 represents the enumeration for a macro-free

            ' Excel 2007 Workbook (.xlsx).

            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51

            ' Code 52 represents the enumeration for a

            ' macro-enabled Excel 2007 Workbook (.xlsm).

            Case 52:

               If .HasVBProject Then

                  FileExtStr = ".xlsm": FileFormatNum = 52

               Else

                  FileExtStr = ".xlsx": FileFormatNum = 51

               End If

            ' Code 56 represents the enumeration for a

            ' a legacy Excel 97-2003 Workbook (.xls).

            Case 56: FileExtStr = ".xls": FileFormatNum = 56

            ' Code 50 represents the enumeration for a

            ' binary Excel 2007 Workbook (.xlsb).

             Case Else: FileExtStr = ".xlsb": FileFormatNum = 50

         End Select

      End If

   End If

End With

' Change all cells in the worksheet to values, if desired.

   With Destwb.Sheets(1).UsedRange

      .Cells.Copy

      .Cells.PasteSpecial xlPasteValues

     .Cells(1).Select

   End With

 Application.CutCopyMode = False

'Save the new workbook and then mail it.

   TempFilePath = Environ$("temp") & ""

   TempFileName = Destwb.Sheets("ASNE PM Check List").Range("b8").Value & "  ASNE PM   " & Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb

   .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

      On Error Resume Next

      For I = 1 To 3

        .SendMail "******@power.com", _

        "ASNE PM CHECK LIST"

            If Err.Number = 0 Then Exit For

      Next I

      On Error GoTo 0

   .Close SaveChanges:=False

End With

' Delete the file you just sent.

Kill TempFilePath & TempFileName & FileExtStr

With Application

   .ScreenUpdating = True

   .EnableEvents = True

End With

      'Protecting all worksheets with password

      For Each objSheet In Worksheets

          If objSheet.ProtectContents = False Then objSheet.Protect "VZW-ASNE"

      Next objSheet

End Sub

Answer 1

That command works as expected for me in XL2010.

I see in the code that you are unprotecting worksheets; are there any other types of protection in place that might affect the copy? (I'm not sure which might cause problems, but I'd check sharing, workbook-level protection, is the password unprotect line working as expected, if the file is opened from a network or email or other location whether the "enable editing" message is showing, etc. 

 I'd also suggest that you comment out the screenupdating and enableevents lines, so you can see what is happening while testing

Answer 2

there are 15 worksheets in this workbook.  I am running the same code for 15 different macros with differences after the failed code line.  13 of 15 run perfectly.  only 2 sheets have this code failure.  as i said all 15 macros start with teh same code and go 10 more lines before there are code differences in each macro.

for some reason ActiveSheet.Copy cannot find the Active Sheet?????

the error is the same on both sheets that fail.  :Run-time error '32809'

what code can i use to see what the name of the active sheet is?

what other debug things can i do?

Thanks for your help, just frustrated.

Bob

Answer 3

for some reason ActiveSheet.Copy cannot find the Active Sheet?????

the error is the same on both sheets that fail.  :Run-time error '32809'

Close Excel, be sure all updates are installed, delete all *.exd files on your PC and try again.

Andreas.

Answer 4

I found the answer on another blog.

I exported the macro modules, saved the xlsm as a xlsx, opened the xlsx workbook, reimported the modules, saved as xlsm.  when I reopened the macros recompile.  all works fine.