Same macro - Different Worksheets!?

P

PaulW

I have a spreadsheet that looks has two criteria.

1. Must not go over a set number
2. Must not allow entry past a certain date.

This code is working but i have serveral places (one on each worksheet) that
this needs to work for.

Eg/ Booking in for Manchester, Bristol, Birmingham and Sheffield, i have a
workbook one for each place.

When i place the code in each worksheet (its a worksheet_calculate macro) it
reads the information from the other worksheets and will not work.

i also tried placing the code within the ThisWorkbook section but the code
just does not work!

Any ideas as to how i can get this code to work?

thanks
PaulW
 
P

PaulW

Please find the code below, i am useing activeSheet / Cell / Address to
define the different sheets

Private Sub Worksheet_Calculate()

today = Range("B4").Value
shee = ActiveSheet.Name
rang = ActiveCell.Column
addres = ActiveCell.Address
Valu = Sheets(shee).Cells(5, rang + 3).Value
dat = Sheets(shee).Cells(6, rang + 1).Value
dat2 = Sheets(shee).Cells(6, rang + 6).Value
num = 0

If rang = 2 Then num = 8
If rang = 8 Then num = 14
If rang = 14 Then num = 2
If num = 0 Then Exit Sub

If Valu > Range("J1").Value Then
MsgBox ("The Number has reached its limit. Please use the next slot. (" &
dat2 & ")")
Selection.End(xlUp).Select

addres = ActiveCell.Address
act = Range(addres).Value

Selection.ClearContents
Sheets(shee).Cells(10, num).Select
ActiveCell.Value = act

Else: End If

If today > dat Then
MsgBox ("The Cut off point has been reached for this slot. Please use the
next available slot. (" & dat2 & ")")
Selection.End(xlUp).Select

addres = ActiveCell.Address
act = Range(addres).Value

Selection.ClearContents
Sheets(shee).Cells(10, num).Select
ActiveCell.Value = act

Else: End If

End Sub
 
Top