(VBA) Duplicate sheet and rename the copied sheet

[Virgule]

Hi,

I'm trying to feed information from a MSProject file to an excel workbook. I want that for every uncompleted task that is a non-summary task, that I duplicate a sheet in the excel workbook and rename it to the Text1 field value of the task.

The template sheet is the 2nd sheet and is named "Template".
ligne is a counter
dernier_nom is a string that is the name of the last created sheet, so I can place the copied sheet at the end
I'm running a french version of both software so Project_Task.Summary="Faux" means "False" or "No"

Here's my code so far :


Sub CréationOnglets()

Application.ScreenUpdating = False


Dim mpApp As MSProject.Application
Dim Proj As Project
Dim ligne As Integer
Dim Project_Task As Task
Dim dernier_nom As String



Set mpApp = New MSProject.Application
mpApp.Visible = True
AppActivate "Microsoft Project"
dernier_nom = Template



ligne = 0


For Each Project_Task In mpApp.ActiveProject.Tasks
If Not Project_Task Is Nothing Then
If Project_Task.Percentcomplete <> 100 Then
If Project_Task.Summary = "Faux" Then

ActiveWorkbook.Sheets(2).Copy _
After:=ActiveWorkbook.Sheets(dernier_nom)

Sheets(ligne + 3).Name = Project_Task.Text1

dernier_nom = Project_Task.Text1

ligne = ligne + 1


End If
End If
End If
Next Project_Task






End Sub


I'm getting an error on lines :
ActiveWorkbook.Sheets(2).Copy _
After:=ActiveWorkbook.Sheets(dernier_nom)

Can someone help me ?
Thanks,

 

[Virgule]

bump

 

[Virgule]

Fixed it :

Sub CréationOnglets()

Application.ScreenUpdating = False


Dim mpApp As MSProject.Application
Dim Proj As Project
Dim ligne As Integer
Dim Project_Task As Task
Dim dernier_nom As String



Set mpApp = New MSProject.Application
mpApp.Visible = True
AppActivate "Microsoft Project"
dernier_nom = Template



ligne = 0


For Each Project_Task In mpApp.ActiveProject.Tasks
If Not Project_Task Is Nothing Then
If Project_Task.Percentcomplete <> 100 Then
If Project_Task.Summary = "Faux" Then


Sheets("Template").Visible = True
Sheets("Template").Select
With ActiveWorkbook.Sheets
ActiveWorkbook.Sheets("Template").Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
End With

Sheets(ligne + 3).Name = Project_Task.Text1

dernier_nom = Project_Task.Text1

ligne = ligne + 1

If ligne = 3 Then Exit Sub

End If
End If
End If
Next Project_Task






End Sub