Microsoft Access Office VB VBA Help and Examples

Setting a worksheet name in code

If you are writing a macro which creates a new sheet in a workbook, it is not at all obvious how you rename it once you've created it.  Try this

    Dim ws As Worksheet 'Create a worksheet object
    Set ws = Sheets.Add  'set worksheet Object to new sheet
    ws.Name = "NewName"  'rename sheet

This is not obvious from the help except that the help says the new method RETURNS a worksheet object.  If something is returned you can set a reference to it using (yes you've guessed it) SET. Now you have an object referencing this sheet you can do what you like with it.

The easiest way to continue your macro from here is to record a macro then change all references to ActiveSheet to WS.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.