Why doesn't this simple VBA code work in Excel?

user_14305354 0
Viewed 103

I'm quite new to programming with VBA (or any language, let's be honest). I'm trying to do a project for work, adding short sections at a time to see if the code still works, and trying out new code in a separate Sub. I've come across an error that I can't get around. The results don't change when they're the only line in a separate Sub.

The following code works:

ActiveWorkbook.Sheets("Template").Copy After:=ActiveWorkbook.Sheets("Student info")

Whereas the following code, when run, breaks with a 424 run-time error (object required). I've tried selecting instead of naming, still no luck. It does successfully copy the worksheet to the correct place, despite the error, but is called 'Template (2)'.

ActiveWorkbook.Sheets("Template").Copy(After:=ActiveWorkbook.Sheets("Student info")).name = "newname"

This is very confusing because the code below does work. Is it just that trying to name something after using 'add' does work, but after 'copy', it doesn't?

ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets("Student info")).name = Student_name

Thanks in advance for any help.

vba excel
1 Answers

The reference (to the created copy) as return value (of a function) would be useful, but as Worksheet.Copy is a method of one worksheet (in opposite to Worksheets.Add what is a method of the worksheets-collection), they didn't created it. But as you know where you created it (before or after the worksheet you specified in arguments, if you did), you can get its reference by that position (before or after).

In a function returning the reference:

Public Enum WorkdheetInsertPosition
    InsertAfter
    InsertBefore
End Enum

Public Function CopyAndRenameWorksheet(ByRef sourceWs As Worksheet, ByRef targetPosWs As Worksheet, ByVal insertPos As WorkdheetInsertPosition, ByVal NewName As String) As Worksheet
    'If isWsNameInUse(NewName) then 'Function isWsNameInUse needs to be created to check name!
        'Debug.Print NewName & " alredy in use"
        'Exit Function
    'End If

    With sourceWs
        Dim n As Long
            Select Case insertPos
                Case InsertAfter
                    .Copy After:=targetPosWs
                    n = 1
                Case InsertBefore
                    .Copy Before:=targetPosWs
                    n = -1
                Case Else
                    'should not happen unless enum is extended
            End Select
    End With

    Dim NewWorksheet As Worksheet
    Set NewWorksheet = targetPosWs.Parent.Worksheets(targetPosWs.Index + n) 'Worksheet.Parent returns the Workbook reference to targetPosWs
    NewWorksheet.Name = NewName ' if name already in use an error occurs, should be tested before
    Set CopyWorksheetAndRename = NewWorksheet
End Function

usage (insert after):

Private Sub testCopyWorkSheet()
    Debug.Print CopyAndRenameWorksheet(ActiveWorkbook.Sheets("Template"), ActiveWorkbook.Sheets("Student info"), InsertAfter, Student_name).Name
End Sub

to insert the copy before the target worksheet, change third argument to InsertBefore (enumeration of options). New Worksheet.Name needs to be unique or you'll get an error (as long you not implemented the isWsNameInUse function to check that).

Also note that there is a difference between .Sheets and .Worksheets

You can get the links to the documentation by moving the cursor (with mouse left-click) in the code over the object/method you want more infos on and then press F1

user_94393300
Related