Copying a Sheet with a Chart "Run-time error"

[birnzy]

Hi everyone, first post here but been looking at the forums for help for a while.

I've been working on automating functions in an Excel Workbook. The workbook is used for tracking the progress resources are making in a project. Each resource has three sheets of data so when starting a project I have made a macro to automatically create these sheets by copying templates.

The user gives a number to show how many resources are being added as well as the resource name and its task (I'm sure I could just count the names but I consider it an added error check to make sure this bit is filled in correctly).

The issue I'm having is if more than one resource is being added my macro crashes upon copying in the second sheet, which contains a chart, for the second vessel. Upon this happening I am shown an error message of a Run-time error and a large number that I haven't found online elsewhere ('-2147352565 (8002000b)'). The actual message has changed, originally it said something about a problem with the chart dimensions but now it says This property is only used by value axes. I'm sure this would mean that the error code has also changed slightly.

After hunting around I can't find this exact issue so I'm hoping you folks can help me out to get to the bottom of it!

A snippet of the code:

Sub build_workbook()


Dim vesselNo As Integer
vesselNo = ActiveWorkbook.Sheets("Get Started").Range("F2")


Dim count As Integer
count = 1


Dim vesselName As String
vesselName = "vessel"


Dim vesselTask As String
vesselTask = "Task"


Dim lastRow As Long
lastRow = 1


For count = 1 To vesselNo


vesselName = ActiveWorkbook.Sheets("Get Started").Range("I" & (count + 2)).Text
vesselTask = ActiveWorkbook.Sheets("Get Started").Range("J" & (count + 2)).Text


ActiveWorkbook.Sheets("Template").Copy After:=ActiveWorkbook.Sheets("Graphs")
ActiveWorkbook.Sheets("Spent Template").Copy After:=ActiveWorkbook.Sheets("Template (" & (count + 1) & ")") 'here is where it stops
ActiveWorkbook.Sheets("Split Template").Copy After:=ActiveWorkbook.Sheets("Spent Template (" & (count + 1) & ")")

It is worth mentioning there is code that comes after this that changes the name of the sheet and some formulas before the code loops so I don't think this is the issue. Any more info you need let me know!

 

[Worf]

Welcome to the Board

Can you post a link to the workbook, with sensitive data changed?

 

[birnzy]

Thank you!

Unfortunately it would take quite a lot of work to remove sensitive data as there is quite a bit and I would not be able to post it online I'm afraid.

I appreciate you getting back to me so quickly however!

 

[Worf]

That error will be generated if the referenced sheet does not exist. Try this:

Sub birn()
Dim count, sn$
count = 9
sn = "Template (" & (count + 1) & ")"
On Error Resume Next
sn = ActiveWorkbook.Sheets(sn).Name
If Err.Number <> 0 Then
    MsgBox "This sheet does not exist!", vbCritical, sn
    Exit Sub
End If
ActiveWorkbook.Sheets("Spent Template").Copy After:=ActiveWorkbook.Sheets(sn)
On Error GoTo 0
End Sub