How to copy and paste a sheet in vba?

Question

I'm searching how i can copy the activesheet in vba , i'm wanting to paste it in another workbook ( which is closed ).

Normaly i tried this but it fails :

 Workbooks.Open Filename:=ThisWorkbook.Path & "\target.xlsx" 
ThisWorkbook.ActiveSheet.Copy After:=Workbooks("target.xlsx").Sheets(Workbooks("target.xlsx").Worksheets.Count)

Does this answer your question? [How to copy only a single worksheet to another workbook using vba](https://stackoverflow.com/questions/20246465/how-to-copy-only-a-single-worksheet-to-another-workbook-using-vba) — braX, Dec 09 '19 at 21:01
I have this error " l'indice n'appartient pas à la selection " — Tou Mou, Dec 09 '19 at 21:07
I tried this : Workbooks.Open Filename:=ThisWorkbook.Path & "\target.xlsx" ThisWorkbook.ActiveSheet.Copy After:=Workbooks("target.xlsx").Sheets(Workbooks("target.xlsx").Worksheets.Count) — Tou Mou, Dec 09 '19 at 21:12
[`Workbook.Close`](https://learn.microsoft.com/fr-fr/office/vba/api/excel.workbook.close). — BigBen, Dec 09 '19 at 21:13
@BigBen this will close all the opened workbooks , i'm wanting to close just the target.xlsx — Tou Mou, Dec 09 '19 at 21:15
I try it. it closes all the opened workbooks after displaying a message ! — Tou Mou, Dec 09 '19 at 21:20
I'm wanting to copy and paste the activesheet at the end of another workbook but this is not working for me ! — Tou Mou, Dec 09 '19 at 21:24
Please [edit] your question with your attempt to use `Workbook.Close`. — BigBen, Dec 09 '19 at 21:28

score 4 · Accepted Answer · answered Dec 09 '19 at 22:00

Workbooks.Open is a function. It returns a Workbook object reference, pointing to the Workbook object that was just opened.

Workbooks.Open Filename:=ThisWorkbook.Path & "\target.xlsx"

You are discarding that reference.

Capture it!

Dim targetBook As Workbook
Set targetBook = Workbooks.Open(ThisWorkbook.Path & "\target.xlsx")

Now instead of dereferencing that workbook from the Workbooks collection everytime you need it...

ThisWorkbook.ActiveSheet.Copy After:=Workbooks("target.xlsx").Sheets(Workbooks("target.xlsx").Worksheets.Count)

Simply use the object you've got:

ThisWorkbook.ActiveSheet.Copy After:=targetBook.Sheets(targetBook.Worksheets.Count)

And when you're done, invoke it's Close method to close it:

targetBook.Close SaveChanges:=True

Thank you a lot @Mathieu Guindon You solved the question. – Tou Mou Dec 10 '19 at 21:11 — Tou Mou, Dec 10 '19 at 21:11

How to copy and paste a sheet in vba?

1 Answers1