Method 'Copy' of object '_Worksheet' failed - Run-time Error

[slieberg]

Hi,

I have an Excel tool that is currently working on over 300 user's machines but is failing on 2 specific peoples. The code in question, creates a new Excel workbook, then copies over a worksheet and then emails it. Both of the users having problems have Excel 2007. Does anyone have any idea what is going on? The run-time error text is '-2147417848 (80010108) Method 'Copy' of object '_Worksheet' failed.

See below for code...

Sub emailFunction(choice As String) 'choice is outlook or webmail
'****
'Emails action plan & Dashboard (and if flag set - Extra pages
'****
Application.ScreenUpdating = False
On Error GoTo 0 'EmailError
'ThisWorkbook.Save

'Variable declaration
Dim oApp As Object, _
oMail As Object, _
wb As Workbook, _
FileName As String, MailSub As String, MailTxt As String
Dim strname As String
Dim propName As String
Dim currentfile As Workbook
Set currentfile = ThisWorkbook

'Create a new workbook and Copy over the Dashboard and Action Plan
ThisWorkbook.Activate
Application.Wait (200)
Sheets("Dashboard").Select
DoEvents
Sheets("Dashboard").Copy - THIS IS THE LINE IT DIES ON


Application.ScreenUpdating = False



Set wb = ActiveWorkbook

'Create Action Plan Tab
wb.Sheets.Add
ActiveSheet.name = "Action Plan"
ActiveWindow.DisplayHeadings = False
'COPY Action Plan content
Dim actionr As Range
currentfile.Sheets("Action Plan").unprotect
Set actionr = currentfile.Sheets("Action Plan").Cells

'paste in new workbook

actionr.Copy
wb.Sheets("Action Plan").Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wb.Sheets("Action Plan").Paste
currentfile.Sheets("Action Plan").protect DrawingObjects:=True, Contents:=True, Scenarios:=True
currentfile.Sheets("Action Plan").EnableSelection = xlUnlockedCells

FileName = "Sample File".xls"



Sheets("Dashboard").Select
'Copy over color palette
wb.Colors = currentfile.Colors

.... more code from here.


Any thoughts?

 

[JackDanIce]

Try changing that to:

Sheets("Dashboard").Copy Before:=Sheets("Dashboard (2)")
ActiveSheet.Name = "New Dashboard"

 

[slieberg]

But I need the copied Dashboard worksheet to be in a new file....wouldn't your suggestion just copy it in the current workbook?