vba copy rename worksheet in Excel

Q168. In Excel, how can I copy and rename a WorkSheet using VBA?

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

Description:

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 do I restrict user entry in ActiveX Text Box to just numeric values?

2) Vba length of an array in Excel

3) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

4) How can I check if a file exists in a folder using VBA?

5) String split in vba in Excel

6) Line break in vba message box in Excel

7) Vba delete entire row if contains certain text in Excel

8) How can I export a chart as a gif file?

9) How can I hide all comments in my WorkSheet using VBA?

10) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

11) Userform initialize vs userform show in Excel

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

13) How do I update my DropDown list whenever the sheet is activated?

14) How can I extract file name from a full path including folder path and file name?

15) How can I set the Source Data of charts using VBA?