VBA copy rename worksheet in Excel

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub CopyAndNameWorksheet()
  3. ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  4. ActiveSheet.Name = "Copied Sheet"
  5. End Sub


a) Line 3 - ThisWorkbook.Sheets.Count represents the last sheet. ActiveSheet is copied and moved to end of the workbook.

b) Line 4 - Since by default after copying sheet, the copied sheet is activated, ActiveSheet object could be used to rename the copied WorkSheet.


You can find similar Excel Questions and Answer hereunder

1) How can I delete all shapes in a WorkSheet?

2) String split in VBA in Excel

3) How can I hide a specific Shape in my WorkSheet?

4) Tables in Excel VBA. How you can create table in VBA and work with them with macros

5) How to code more simply in VBA. Use of Keywords is helping a lot

6) How can I filter and copy only filtered data using VBA?

7) What are worksheets events in VBA. Here we explain some of the main one like like activate or deactivate

8) How can I find the number of working days between 2 dates using VBA?

9) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

10) How to do worksheet protection with VBA in Excel


Here the previous and next chapter