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:
- Option Explicit
- Sub CopyAndNameWorksheet()
- ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
- ActiveSheet.Name = "Copied Sheet"
- 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.