Same macro - Different Worksheets!?
 

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

Same macro - Different Worksheets!?
 

Hi Paul,

Do we have the names of the sheets in the code? Or is it a generic code
should should work for all the sheets.


Regards
Ankur
www.xlmacros.com

On Dec 12, 4:25 pm, PaulW wrote:
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

Same macro - Different Worksheets!?
 

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


"ankur" wrote:


Hi Paul,

Do we have the names of the sheets in the code? Or is it a generic code
should should work for all the sheets.


Regards
Ankur
www.xlmacros.com

On Dec 12, 4:25 pm, PaulW wrote:
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