VBA COPY sheet's data in an other Workbook

[slaim]

Hello everyone

I've a problem with the copy of a data present in a book to another one.


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



Here you can read my code:

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


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).Range


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


-_-_-_-_-_-_-_-_-_-_-_

Thanks for your help...!

 

[slaim]

Maybe I should explain it better... I'm looking for an answer to my problem with Copy since 1 weeks...

I try to copy data from the template excel doc to the yearly planing doc where I write in sheets according to the "Month".

xlBook5.Sheets(ws5.Name).Copy xlBook6.Sheets(ws6.Name) This does'nt work and I really don't know why!

thanks for your help