vba: copy sheet with named destination

Question

I want to create a copy of a sheet with data. I managed to create a copy with this code, but want to create it with a defined name

Dim outsheet As String outsheet = "SAP Import" Dim wsCopy As Worksheet Set wsCopy = ThisWorkbook.Worksheets(outsheet) wsCopy.Copy After:=ThisWorkbook.Worksheets(outsheet)

Now I would like to change the name. The obvious solution is however not implemented in VBA (Parameter unknown)

Dim strSheetTemp As String strSheetTemp = outsheet + "-temp" wsCopy.Copy Destination:=ThisWorkbook.Worksheets(strSheetTemp)

How can I create a named copy? Or how can I get the name of the copy to rename it ?

That is not the main problem. It does not compile because Destination is not known as an argument. — Matthias Pospiech
– Matthias Pospiech, Commented Sep 9, 2019 at 7:03
Destination is not known, because sheet with name 'strSheetTemp' does not exist yet, look at @horst answer, that should work. — Teamothy
– Teamothy, Commented Sep 9, 2019 at 7:09

horst · Accepted Answer · 2019-09-09 07:04:49Z

I believe the sheet can only be named (or renamed in this case) after being copied.

Dim outsheet As String outsheet = "SAP Import" Sheets(outsheet).Copy After:=Sheets(outsheet) Sheets(Sheets(outsheet).Index + 1).Name = outsheet & "-temp"

David García Bodego · Accepted Answer · 2019-09-09 10:59:10Z

Try this one:

Dim outsheet As String outsheet = "SAP Import" Dim wsCopy As Worksheet Set wsCopy = ThisWorkbook.Worksheets(outsheet) wsCopy.Copy After:=wsCopy ActiveSheet.Name = outsheet & "-temp"

Following your trial philosophy...

In order to change the name you should use the property .Name. That one will work in your case.

Hope it helps

Shorter code by reference to set sheet variable wsCopy (and avoiding possible confusion between ActiveWorkbook and ThisWorkbook) :-; wsCopy.Copy After:=wsCopy

Collectives™ on Stack Overflow

vba: copy sheet with named destination

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related