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.

 

[Trebor76]

Hi Fieldsdj,

There is no standard VBA function called Contains I think you need to use the InStr function.

Robert

 

[Fieldsdj]

Thanks for replying. I'm not familiar with the InStr function. Can you explain how I would use it?

 

[Trebor76]

Here's a good overview of the function. As well as the examples shown on that site here's one way of how you'd use Instr:

Option Explicit
Sub Macro1()

    If InStr(Format(Now(), "dddd"), "Sat") > 0 Then
        MsgBox "Today is Saturday"
    End If

End Sub

Regards,

Robert

 

[mikerickson]

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]

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?

I honestly don't know what it's supposed to do. I had someone on here help me with the original code, which was this one.

Sub Button1_Click()
Sheets("Template").Visible = True
 Dim numrows As Long, i As Long
 Dim sname As String
 With Sheets("Sheet1")
 numrows = WorksheetFunction.CountA(.Range("D5:D55"))
 If numrows = 0 Then Exit Sub
 For i = 1 To numrows
 If Contains(Sheets, .Cells(i + 4, "D").Value) Then
 sname = .Cells(i + 4, "D").Value
 Call MsgBox(sname & " Sheet name already exists. Remove from List and try again", vbCritical, Application.Name)
Exit Sub
End If
ThisWorkbook.Sheets("Template").Copy After:=Sheets(Sheets.Count)
 ThisWorkbook.Sheets(Sheets.Count).Name = .Cells(i + 4, "D").Value
 Next
 Sheets("Template").Visible = False
 End With
 End Sub

This code will take the names in a range of cells and when a button is clicked it will copy a template and create a sheet for each name and change the tab name to the names in the list. I decided I didn't want a list of names after all so I tried to alter the code to work with just one name in one cell and do the same thing. I changed the range of D5:D55 to just B7 but it didn't work. I hope I'm making sense. Sorry if I'm confusing what I'm trying to do.

 

[MARK858]

Was there another code starting with the word function in the original solution?

 

[Fieldsdj]

Was there another code starting with the word function in the original solution?

There was no other code.

 

[MARK858]

In a previous thread I can see dave3009 correcting your sheet reference but I can't see where the Contains word got proposed

 

[Fieldsdj]

I found the original code on the web as I was looking for a solution to what I needed. It already had the Contains line in there and it worked after dave3009 helped me with it. As soon as I started messing with it, I started getting the error. I even went back to the original code using the range of names and I still get the error at the contains line. I'm really confused now. This is just the beginning of the coding I'm going to need for my complete project. I think I may be in over my head.