CODEWITHSUNDEEP
vbaexcel
sharath
sharath

Reputation: 1

Object Required Error in excel VBA while trying to rename the sheet

I am trying to copy a particular sheet and rename it to final. the code is as follows : 

Sub CreateSheet()
  Worksheets("Key metrics").Copy(Before:=Worksheets(1)).Name = "Final"
End Sub

The program executes till copying the sheet with the contents but then it throws the error and doesn't rename my new sheet to final.

thanks in advance.

Upvotes: 0

Views: 1113

Answers (2)

RJW
RJW

Reputation: 19

Sub CreateSheet()
Dim WS As Worksheet

Worksheets("Key metrics").Copy Before:=Worksheets(1)
Set WS = ActiveSheet
WS.Name = "Final"

End Sub

Hope that helps.

Upvotes: 0

whytheq
whytheq

Reputation: 35557

The problem is in connection with the nesting that you're carrying out. Copy does not return a sheet object, hence your error.

Try this:

Sub CreateSheet()
  with Excel.thisworkbook
    .Worksheets("Key metrics").Copy Before:=.Worksheets(1)
    .activesheet.Name = "Final"
  end with
End Sub

Here is a QA that confirms this:
Why does Worksheet.Copy not return a reference to the new workbook created

If this is going to be a small self-contained helper routine you could make it a function with a worksheet return type:

Sub tst()

Dim mySht As Excel.Worksheet
Set mySht = crtSheet("foobar", "barfoo") '<<used via this

End Sub

Function crtSheet(ByVal tgtSheet As String, ByVal newName As String) As Excel.Worksheet
  Dim s As Excel.Worksheet
  With Excel.ThisWorkbook
    .Worksheets(tgtSheet).Copy Before:=.Worksheets(1)
    Set s = .ActiveSheet
    s.Name = newName
  End With
  Set crtSheet = s
End Function

Upvotes: 2

Related Questions