Copy and rename an Excel Worksheet

There are a number of methods aside from ‘Right clicking’ a tab and choosing move or copy in order to copy a worksheet in Excel. Using VBA can be a very quick way of completing this task especially if you have multiple worksheets to copy or need multiple copies added. You can even specify whereabouts you want the copied worksheet to be placed, and rename the new worksheet if applicable. Renaming as text can be cumbersome where multiple renames need to be done, but you can use a for-next loop with numbers to rename as incremental numbers.

The short piece of VBA code below does just this. It copies a worksheet named sheet1 and copies it after sheet2 then renames it. Feel free to contact us with any assistance you need on your Excel project using the contact form or leaving a comment below. A few related worksheet move/copy code snippets are included here for ease of reference, and further codes added to the comments section.


Sub Copy_and_Rename_worksheet
Worksheets(“Sheet1”).Copy After:=Worksheets(“Sheet3”)
ActiveSheet.Name = “New Name”
End Sub


Related tips for this area of Excel development are adding sheets and generally naming them. This code below will add a sheet and name it ‘Steve’.


Sheets.Add.Name = “Steve”


Move the first sheet to the end / after sheet2 / after 3rd sheet in the workbook


Sheets(1).Move after:=Sheets(Sheets.Count)

Sheets(1).Move after:=Sheets(“Sheet2”)

Sheets(1).Move after:=Sheets(3)


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *