Compile Error: Sub or Function not defined

Fieldsdj

Board Regular
Joined
Feb 27, 2012
Messages
65
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.

Code:
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
Hi Fieldsdj,

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

Robert
 

Fieldsdj

Board Regular
Joined
Feb 27, 2012
Messages
65
Thanks for replying. I'm not familiar with the InStr function. Can you explain how I would use it?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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:

Code:
Option Explicit
Sub Macro1()

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

End Sub

Regards,

Robert
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906

ADVERTISEMENT

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

Board Regular
Joined
Feb 27, 2012
Messages
65
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.
Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
In a previous thread I can see dave3009 correcting your sheet reference but I can't see where the Contains word got proposed :confused:
 

Fieldsdj

Board Regular
Joined
Feb 27, 2012
Messages
65
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.:oops:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,539
Messages
5,596,754
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top