Ambiguous name detected

[coliervile]

Can anyone explain why when I type Sub TestCharliePeters() I receive a Compile error: Ambiguous name detected: TestCharliePeters? I can type TestCharlesPeters or TestCharleyPeters and it works fine...just wondering why. All of the other names work in sheet1 of the workbook. See attach test workbook:


Sub TestCharliePeters()
Dim Rng As Range
Dim Sh As Worksheet
Dim c As Range
Dim x As Integer
Set Rng = Worksheets("sheet1").Range("A1:A" & Range("A65536").End(xlUp).Row)
Set Sh = Worksheets("sheet10")
' Delete existing data
Sh.Range("A2:A" & Range("A65536").End(xlUp).Row).EntireRow.Clear
x = 2
For Each c In Rng
If c.Value = "CharliePeters" Then
c.EntireRow.Copy Sh.Cells(x, 1)
x = x + 1
End If
Next c
End Sub

Also is there a way to run this when the main database in sheet1 is updated or new data is added through a VB userform? Basically the code copies rows of data to a designated worksheet from worksheet1 when the criteria is met in Column A.

Charlie

[malik641]

Welcome to VBAX Charlie,

It's cause you have 2 subs with the same name...CharliePeters...and VBA doesn't like more than one CharliePeters ;)

As for your second question, you can use a WorkSheet_Calculate event to run the macro you want. Or you can use a Workbook_SheetCalculate if you want to check for various sheets.

BTW, please change the title of your thread to suit your need better. If people didn't do that I think that most threads would be titled "Why does this happen!!!!!!!!! AAAAH!!!!!!"

[coliervile]

Thanks malik641- in the future I will post a more acurate "Name" to suit the subject matter.

Why would all of the other names work and not the particular one...is Excel VBA that odd sometimes?

Thanks for your other recommendation and I'll look-up some topics on it.

Charlie

[malik641]

Why would all of the other names work and not the particular one...is Excel VBA that odd sometimes?
You must have read my post too fast. I said it's because there are 2 subs (procedures/macros) with the same name, being CharliePeters. And you cannot have 2 subs/procedures/macros with the same exact name, which is why the other "versions" of CharliePeters worked for you.

[coliervile]

Thanks malik641

[malik641]

Anytime Charlie :yes

Don't forget if your question is resolved, please mark your thread "Solved" by using the thread tools when inside the thread (viewing posts). :thumb