discarnet

thanks and sorry about the code tags got it working now after several hours hard work now checks 5 rotas for 7 days and looks like this Sub search() Dim strRotaTop As String Dim strUserInput As String R = 1 RO = 1 INP = ("ENTER NAME FOR ROTA ") PROMPT = vbNewLine & vbNewLine & ("TYPE NEXT TO EXIT TO NEXT ROTA") PROMPT = PROMPT & vbNewLine & vbNewLine PROMPT = PROMPT & ("TYPE QUIT TO EXIT SET UP") ROTA = ("E1A") RESET: strUserInput = InputBox(INP & ROTA & R & PROMPT, "ENTER NAMES") If strUserInput = ("QUIT") Then GoTo LAST If strUserInput = ("NEXT") Then GoTo LOOPROTA LOOPCELL: For W = 1 To 200 Step 1 If W >= 200 Then GoTo LOOPDAY CE = ("E") & W strRotaTop = CE Range(strRotaTop).Activate If ActiveCell = ROTA & R Then GoTo NAME If ActiveCell = ROTA & ("0") & R Then GoTo NAME NEX: Next W GoTo LOOPROTALINE NAME: ActiveCell.Offset(0, 1).Activate ActiveCell = strUserInput ActiveCell.Select Selection.Interior.ColorIndex = xlNone If strUserInput = ("") Then GoTo COLOUR GoTo NEX LOOPDAY: Da = Da + 1 If Da = 8 Then GoTo LOOPROTALINE If Da = 1 Then Sheets("SUNDAY").Activate If Da = 2 Then Sheets("MONDAY").Activate If Da = 3 Then Sheets("TUESDAY").Activate If Da = 4 Then Sheets("WEDNESDAY").Activate If Da = 5 Then Sheets("THURSDAY").Activate If Da = 6 Then Sheets("FRIDAY").Activate If Da = 7 Then Sheets("SATURDAY").Activate GoTo LOOPCELL LOOPROTALINE: Da = 0 R = R + 1 GoTo RESET LOOPROTA: RO = RO + 1 If RO = 1 Then ROTA = ("E1A") If RO = 2 Then ROTA = ("E2A") If RO = 3 Then ROTA = ("L1A") If RO = 4 Then ROTA = ("L2A") If RO = 5 Then ROTA = ("N1A") If RO = 6 Then GoTo LAST R = 1 GoTo RESET COLOUR: With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With GoTo NEX LAST: ActiveWorkbook.Save MSG = (" SCHEDULE SET UP COMPLETE ") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & ("DON'T FORGET TO CHECK FOR SICKNESS AND ABSENCE") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & (" AND UNALLOCATED ROTAS ") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & (" ON A DAILY BASIS ") K = MsgBox(MSG, vbOKOnly, "DONE") End Sub thanks for your help

OK I'M STILL NEW TO VB In coloum A is a list of rota lines (e1a1, e1a2 ect) the input box ask for the name of the person on each rota line in turn, and then places thier name in the cell next to thier rota line. This works fine for the first input, but how can i change the Cell.find(what:= so that it will search for the cells contaning the value of rota (["e1a"] +ro) so 1st find is for e1a1 second find is for e1a2, e1a3 and so on SUB ROTA() COU = 0 RO = 0 start: RO = RO + 1 rota = ("e1a") & ro ent = ("enter name for rota e1a") ent = ent & RO E1A = InputBox(ent, "ENTER NAMES") If E1A = "QUIT" Then Exit Sub FINDER: On Error GoTo start If COU = 3 Then GoTo start Cells.Find(What:="e1a1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select ActiveCell = E1A COU = COU + 1 GoTo FINDER GoTo START End Sub

DON'T WORRY ABOUT IT WORKED IT OUT Sub na() ro = 1 START: For x = 5 To 100 ent = ("enter name for rota e1a0") ent = ent & ro e1a = InputBox(ent, "ENTER NAMES") If e1a = "QUIT" Then Exit Sub Cells(x, 2) = e1a ro = ro + 1 Next x End Sub Thanks for looking anyway

Are you trying to copy a compete row if so try this Sub copyrow() Rows("5:5").Select ' change the above row to the one you want to copy Selection.Copy Sheets("Sheet2").Select ' change the "sheet2" to the sheet you want to copy to Rows("1:1").Select ActiveSheet.Paste End Sub this will take a fraction of a second

ok I to work on a server have you tried pointing to the full adress of the file so instead of file://server1/support/file.pdf you link to g/thurrock/support/file.pdf where g is your server drive letter and thurrock would be your server folder if you right click on the file and goto properties it will give you the full address to point to

would love to help you but i can't get the link you provided to work, I to work in a transport office and am working on a sheet for on a simular thyme

Go to file then save as when the save dialog box pops up click tools and general options this will give you options to save with a password needed to modify (read only) or a password to prevent the file being opened

Ok let me try to explain this I have an input box on a loop to enter names, as i have over 200 names to enter on a weekly basis how can i get the macro to move down a cell for each entry from the input box this is what i have so far sub rota() RO = 1 START: ent = ("enter name for rota e1a0") ent = ent & RO E1A = InputBox(ent, "ENTER NAMES") If E1A = "QUIT" Then Exit Sub RO = RO + 1 Worksheets(1).Range("b5").Value = E1A GoTo START End Sub so first time input is for rota e1a01 second time input is for rota e1a02 and so on up to 242 rota numbers from e1a01 to e1a242 are in coloum A names from input box need to be in coloum B so its the ("b5") in Worksheets(1).Range("b5").Value = E1A that i need to increase each time Well i understand what i mean hope you do

Fantastic thank you been trying to work it out all week works great it was the formating the date that confussed me Tim

Hi all Is it possible to save a single spread sheet with a date stamp in the name. for instance i need to archive a spread sheet daily but dont want to lose the information from yesterday. I want to be able to put a 'SAVE' command button on the sheet that saves it as 01/01/06 spreadsheet.xls , 02/01/06 spreadsheet.xls ect I am a complete id*** when it comes to macros/visual basic so a dum dum's guide would be great Thanks

when i used to run xp pro i allways kept a copy of AUTOEXEC.NT on my desktop and copy it back to the system32 folder when needed, this would happen every few months. not been a problem scince upgrade to MCE. you can get the file off the windows cd

Thanks that was what i was thinking glad it wasnt me that brought it in that case does anyone know if and where you can buy full face printable audio cd media

a friend has purchased a standalone audio cd recorder to copy his old audio tapes on to cd, Apparently it can only use AUDIO DISC, standard cdr disk will not record on it, it just says DATA DISK then spits the disc out. Does anyone know what the difrence is between the disc types and is it possible to amend the book type of a data disc so it will be reconised as a audio cdr in the recorder

thats what i was affaid of , Its not in bios on this board, Oh well will have to rig a switch to the mains to stop the power fom there Thanks anyway

Is it possible to turn off USB power when the computer is shut down. The reason i ask is i have a webcam with automatic lights powered by the USB for use in the dark however these lights remain on (and are quiet bright) when the system is shut down