lundi 20 avril 2015

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

Aucun commentaire:

Enregistrer un commentaire