CODEWITHSUNDEEP
vbaexcel
BenSeedGangMu
BenSeedGangMu

Reputation: 221

vba create a new sheet and change name

I am a VBA new user. My purpose is copy a existing worksheet to a new one. And then change the new worksheet name to a proper name. However, I got the VBA Error Message like,

vba runtime error 9 subscript out of range(at the line Set new_ws =Active...)

The VBA code will be the following; thanks in advance

Sub CreateWS()
Dim ws As Worksheet
Dim new_ws As Worksheet

    Set ws = ActiveWorkbook.Worksheets("Bus Voltage")
    ws.Name = "Bus Voltage_All"
    ws.Copy Worksheets(Sheets.Count)
    Set new_ws = ActiveWorkbook.Worksheets("Bus Voltage_All(2)")
    new_ws.Name = "Bus Voltage"
End Sub

Upvotes: 1

Views: 854

Answers (1)

Soulfire
Soulfire

Reputation: 4296

It looks to me like you have an issue with a space.

Your line Set new_ws = ActiveWorkbook.Worksheets("Bus Voltage_All(2)")

Should be

Set new_ws = ActiveWorkbook.Worksheets("Bus Voltage_All (2)")

Notice the space after _All? You were missing that in your code.

A (potentially) helpful tidbit is that when you use the .Copy method, your new worksheet is automatically activated, so you can use this code instead (to avoid errors with the space):

ws.Copy Worksheets(Sheets.Count)
Set new_ws = ActiveSheet

And, on the topic of referencing sheets at all (though not directly related to your question), you can use something called the CodeName to reference the sheet.

For example:

enter image description here

The CodeNames are Sheet1, Sheet2, etc.

The code:

Worksheets("Old Data").Activate

And

Sheet1.Activate

are equivalent.

The major benefit to referencing sheets this way is that if your worksheet name changes, your code won't break.

Upvotes: 3

Related Questions