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

Question

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.

The Worksheets.Add method returns a Worksheet object; Worksheet.Copy returns a boolean value. — Zer0Kelvin
– Zer0Kelvin, Commented Sep 19, 2020 at 12:52
@Zer0Kelvin Just for the sake of correctness .Copy is a sub and returns nothing — ComputerVersteher
– ComputerVersteher, Commented Sep 19, 2020 at 13:36
@ComputerVersteher Just for the sake of fairness: Copy is not a Sub, it is a method of the Worksheet object (you should know the difference) and it returns a boolean. Try to run MsgBox Sheets (1) .Copy (after: = Sheets (Sheets.Count)) — Zer0Kelvin
– Zer0Kelvin, Commented Sep 19, 2020 at 18:54
@Zer0Kelvin seems you are right, but docs and object browser define it as sub. — ComputerVersteher
– ComputerVersteher, Commented Sep 19, 2020 at 19:03

ComputerVersteher · Accepted Answer · 2020-09-19 19:30:43Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related