Compile Error: Sub or Function not defined

[Fieldsdj]

Hello,

Thanks to this forum I was able to get some working code to copy a template worksheet and rename them according to a list of names in a range. So, I changed my project a little and instead of a range I just want to use one cell. I thought I could adjust my code but for some reason it's not working.

Sub Button1_Click()
 Dim numrows As Long, i As Long
 Dim sname As String
 With Sheet2
 numrows = WorksheetFunction.CountA(.Range("B7"))
 If numrows = 0 Then Exit Sub
 For i = 1 To numrows
 If Contains(Sheets, .Cells(i + 6, "B").Value) Then
 sname = .Cells(i + 6, "B").Value
 Call MsgBox(sname & " Sheet name already exists. Remove from List and try again", vbCritical, Application.Name)
Exit Sub
End If
ThisWorkbook.Sheet3.Copy After:=Sheets(Sheets.Count)
 ThisWorkbook.Sheets(Sheets.Count).Name = .Cells(i + 6, "B").Value
 Next
 End With
 End Sub<strike></strike>

So the sheet I want to copy is Sheet3 and what I want the name to be changed to will be in cell B7. I'm getting the error at the "If Contains" line. Thanks for anyone that can help me out.

 

[MARK858]

Contains to me is C++, C or VB.Net code and not VBA. Is it possible to check what language the site you got the code from was working in?

 

[Fieldsdj]

I'll try and find it again but I searched so many sites for days before I found it.

 

[MARK858]

Rather than search for it, your best bet is to just try and answer Mike's question or describe exactly what you are expecting the code to do (the whole code) step by step and then take it from there.

With Sheets as one of the arguments, I don't think InStr is the function that you are looking for.

Fieldsdj, what do you want Contains(Sheets, Sheet2.Cells(i + 6, "B").Value) to do? Under what circumstances is it True?

 

[Fieldsdj]

Ok, so I have a workbook that will start out with two sheets. One will be the main sheet and one will be a template sheet. On the main sheet what i'm trying to accomplish is to have a name entered into cell B7 and when the button is pressed under the name, the template sheet will be copied, renamed to match cell B7, and pasted after the last sheet. I also would like the code to prevent the template sheet from being copied again if the button is pressed a second time with the same name in B7 and to prevent it from being copied if the cell is blank . Maybe have an error box pup up that says the name has to be changed or something.

Overall there will be 15 cells that could have a name in them and I will put a button under each cell that can copy and rename the template sheet.

 

[Fieldsdj]

Ok, so I have something that works for copying and renaming the sheet.

Sub Button1_Click()
    Sheet3.Copy after:=Sheets(ActiveWorkbook.Sheets.Count)
    Sheets(Sheets.Count).Name = Sheet2.Range("B7")
    Sheet2.Activate
End Sub

I just kept removing lines from the code until I figured this out. So, now I want to add the error handling and I'm searching trying to find something that works with my code but I'm having no luck. Can anyone help me with this? I'm looking for a way to make sure when the button is pressed the code causes a message box when the cell is blank, when there is already a sheet with the name, and if there are illegal characters in the name.

 

[Trebor76]

Hi Fieldsdj,

Try this:

Option Explicit
Sub Button1_Click()

    Dim numrows As Long, i As Long
    Dim sname As String
    Dim objMySheet As Object
    
    With ThisWorkbook.Sheets("Sheet2") 'Sheet name for the main sheet. Change to suit if necessary
        numrows = WorksheetFunction.CountA(.Range("B7"))
        If numrows = 0 Then Exit Sub
        For i = 1 To numrows
            On Error Resume Next
                'If the proposed name in cell B[i+6] already exists the in workbook, then...
                Set objMySheet = .Cells(i + 6, "B").Value
                If Err.Number = 0 Then
                    '...put that name to the 'sname' variable and alert the user that it already exists and quit the routine.
                    sname = .Cells(i + 6, "B").Value
                    MsgBox sname & " Sheet name already exists. Remove from List and try again", vbCritical
                    Exit Sub
                End If
            On Error GoTo 0
            'If the code gets here copy the template sheet (set here as 'Sheet3' change to suit if necessary) to the end of this workbook and name it via the text in cell B[i+6]
            ThisWorkbook.Sheets("Sheet3").Copy After:=Sheets(Sheets.Count)
            ThisWorkbook.Sheets(Sheets.Count).Name = .Cells(i + 6, "B").Value
        Next i
    End With
 
End Sub

I've also put in some commentary as to what it's doing to help you if you to need to modify it (say for the name of the main or template tab names).

Regards,

Robert

 

[Trebor76]

This now has error checking for each proposed sheet name in the range:

Option Explicit
Sub Button1_Click()

    Dim numrows As Long, i As Long
    Dim sname As String
    Dim objMySheet As Object
    Dim varInvalidChars As Variant
    
    varInvalidChars = Array(":", "\", "/", "?", "*", "[", "]")
    
    With ThisWorkbook.Sheets("Sheet2") 'Sheet name for the main sheet. Change to suit if necessary
        numrows = WorksheetFunction.CountA(.Range("B7")) + 10
        If numrows = 0 Then Exit Sub
        For i = 1 To numrows
            'Propsed sheet name validation
            If Len(.Cells(i + 6, "B").Value) = 0 Then
                MsgBox "There is no proposed name in cell B" & i + 6 & "." & vbNewLine & "Ensure all cells are filled in and try again.", vbCritical
                Exit Sub
            ElseIf Len(.Cells(i + 6, "B").Value) > 31 Then
                MsgBox "The proposed sheet name in cell B" & i + 6 & " has more then 31 characters." & vbNewLine & "Ensure all cells have a maximum of 31 characters and try again.", vbCritical
                Exit Sub
            ElseIf IsNumeric(Application.Match(.Cells(i + 6, "B").Value, varInvalidChars, 0)) = True Then
                MsgBox "The proposed sheet name in cell B" & i + 6 & " contains one or more invalid characters." & vbNewLine & "Remove any invalid characters: : \ / ? * [ or ] and try again.", vbCritical
                Exit Sub
            ElseIf StrConv(.Cells(i + 6, "B").Value, vbProperCase) = "History" Then
                MsgBox "A tab cannot be named ""History"" as it is a reseved name." & vbNewLine & "Change the name in cell B" & i + 6 & " and try again.", vbCritical
                Exit Sub
            Else
                'If the proposed name in cell B[i+6] already exists the in workbook, then...
                On Error Resume Next 'Stop run-time error 9 error message if the tab doesn't exist
                    Set objMySheet = Sheets(.Cells(i + 6, "B").Value)
                    If Err.Number = 0 Then
                        '...put that name to the 'sname' variable and alert the user that it already exists and quit the routine.
                        sname = .Cells(i + 6, "B").Value
                        MsgBox "There is already a sheet called """ & sname & """ in the workbook." & vbNewLine & "Remove or change the entry in cell B" & i + 6 & " and try again.", vbCritical
                        Exit Sub
                    End If
                On Error GoTo 0
            End If
            'If the code gets here copy the template sheet (set here as 'Sheet3' change to suit if necessary) to the end of this workbook and name it via the text in cell B[i+6]
            ThisWorkbook.Sheets("Sheet3").Copy After:=Sheets(Sheets.Count)
            ThisWorkbook.Sheets(Sheets.Count).Name = .Cells(i + 6, "B").Value
        Next i
    End With
    
    Set objMySheet = Nothing
 
End Sub

Regards,

Robert

 

[Fieldsdj]

Trebor76,
Thanks for the code...when I tried it I get a runtime error and it highlights this line:

With ThisWorkbook.Sheets("Sheet2") 'Sheet name for the main sheet. Change to suit if necessary

Not sure what the problem is.

 

[Trebor76]

You need to change "Sheet2" to the exact name that your main sheet is called (that's why I put the comment "Change to suit if necessary"). The same applies for "Sheet3".

You could use the code name of the tabs if you're OK with that method.

 

[Fieldsdj]

Thank you so much for your time and patience while helping me work this out. Ok, I got most of it to work! I had to change the sheet names to the exact name that is on the tabs, so if someone changes a tab name there will be an error but I don't think anyone will do that so its fine. When I put an invalid character in the name for some reason it gives a run time error '1004' and highlights the line:

ThisWorkbook.Sheets(Sheets.Count).Name = .Cells(i + 6, "B").Value

Any ideas why?

I tried removing ThisWorkbook but got the same error.