Bonjour,

J'ai un soucis avec la méthode "copy". En effet, j'essaie de copier les données d'une feuille se trouvant dans mon book "yearly report" dans un autre classeur en fonction de la date.

Merci pour votre aide!

Copy method of Worksheet class failed" error 1004... (which is a "unfound book " error I think)
Voici mon code:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
debut: 'OPEN THE BOOK: ON ERROR (CANNOT FIND THE BOOK) --> CREATE IT
 
'1) Open the right file
Path = y_year & " " & Client & " - Planning " & ".xlsx"
'MsgBox "Path save: " & Path 'check the name
'Link the path
Set xlBook6 = xlApp6.Workbooks.Open("C:\OPS Contract Tool\Yearly Report\" & Path)
 
On Error GoTo errorws6:
 
ws6debut:
 
MsgBox "WS6 DEBUT"
 
'Read the right sheet
 
name_Sheet = "DR " & Month(Now()) & " " & Year(Now())
Set ws6 = xlBook6.Sheets(name_Sheet)
 
MsgBox "ws6 name " & ws6.Name 'THIS LEADS ME TO THE "errorws6" BECAUSE THE name of the sheet is not existing, so I need to create it by copying in the template book.
 
xlApp6.Visible = True
...
...
...
...
...
...
 
errorws6:
 
If Err.number = 9 Then
 
[INDENT][INDENT]MsgBox "Error number 9"
 
Set xlBook5 = xlApp5.Workbooks.Open("C:\OPS Contract Tool\Yearly Report\Yearly Report.xlsx")
Set ws5 = xlBook5.Sheets("Sheet1")
Set ws6 = xlBook6.Sheets("Sheet1")
Set temp = xlBook6.Sheets.Add
 
MsgBox "copy"
xlBook5.Saved = True
xlBook6.Saved = True
xlBook5.Sheets(ws5.Name).Copy xlBook6.Sheets(ws6.Name)  '    <---- ERREUR ICI 
 
 
temp.Name = "temp" 
 
ws6.Name = "DR " & Month(Now()) & " " & Year(Now())
MsgBox "WS6.name = DR....... " & ws6.Name
 
 
temp.Name = "Sheet1"
MsgBox "Temp : " & temp.Name & "ws6 : " & ws6.Name
 
xlbook6.Saved = True 
 
'free 
xlBook5.Close
xlApp5.Quit
Set ws5 = Nothing
Set xlBook5 = Nothing
Set xlApp5 = Nothing
 
 
End If
 
GoTo ws6debut