Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Compare Two Worksheets? | Excel Discussion (Misc queries) | |||
Saving worksheets using a Macro | Excel Discussion (Misc queries) | |||
Running Same macro in 250 Worksheets in Same Workbook | Excel Worksheet Functions | |||
Applying same macro to all worksheets in workbook | Excel Discussion (Misc queries) | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) |