Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name < Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub
"SangelNet" wrote in message
...
Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

On Jan 7, 1:50*pm, "Otto Moehrbach"
wrote:
This macro will do what you want. *Read the code and ask questions as
needed. *This macro has to go into the ThisWorkbook module of your workbook.
HTH *Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
* * Dim ws As Worksheet
* * If (Sh.Name = "Sheet280" Or _
* * * * Sh.Name = "Sheet283" Or _
* * * * Sh.Name = "Sheet284" Or _
* * * * Sh.Name = "Sheet285" Or _
* * * * Sh.Name = "Sheet286" Or _
* * * * Sh.Name = "Sheet287" Or _
* * * * Sh.Name = "Sheet288") And _
* * * * Target.Column = 20 Then
* * * * If IsEmpty(Target.Value) Then Exit Sub
* * * * For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
* * * * * * If ws.Name < Sh.Name Then
* * * * * * * * With ws
* * * * * * * * * * If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
* * * * * * * * * * * * MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
* * * * * * * * * * * * Exit Sub
* * * * * * * * * * End If
* * * * * * * * End With
* * * * * * End If
* * * * Next ws
* * End If
End Sub"SangelNet" wrote in message

...

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
* *Dim c As Range
* *Dim ilast As Long

* *ilast = cell(Rows.Count, "t").End(xlUp).Row
* *For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
* *If Active.cell.Value = c.Range.Value Then
* *MsgBox ("message here")
* *End If

thnx

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

In this context, the 20 is the number of the column, with Column A being the
1st column. Column T is the 20th column. The code at this point is saying
to take action only if the entry is in Column T and in one of the listed
sheets. HTH Otto
"SangelNet" wrote in message
...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:
This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your
workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name < Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub"SangelNet" wrote in message

...

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

On Jan 7, 9:03*pm, "Otto Moehrbach"
wrote:
In this context, the 20 is the number of the column, with Column A being the
1st column. *Column T is the 20th column. *The code at this point is saying
to take action only if the entry is in Column T and in one of the listed
sheets. *HTH *Otto"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your
workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name < Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub"SangelNet" wrote in message

....

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx

On Jan 7, 9:03*pm, "Otto Moehrbach"
wrote:
In this context, the 20 is the number of the column, with Column A being the
1st column. *Column T is the 20th column. *The code at this point is saying
to take action only if the entry is in Column T and in one of the listed
sheets. *HTH *Otto"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your
workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name < Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub"SangelNet" wrote in message

....

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx

That line should all be on one line in VBA. Otto
"SangelNet" wrote in message
...
On Jan 7, 9:03 pm, "Otto Moehrbach"
wrote:
In this context, the 20 is the number of the column, with Column A being
the
1st column. Column T is the 20th column. The code at this point is saying
to take action only if the entry is in Column T and in one of the listed
sheets. HTH Otto"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your
workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name < Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub"SangelNet" wrote in message

...

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T "
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx