I'm Back --- New Stand Alone Question -Insert Name in List

[want2b]

The easy way would be to insert at the bottom and then sort BUT I don't think this will work because:
I have 55 sheets the last of which totals for all the others.
I need to keep the Names in the same row in each of the 55 sheets.
I search for an account number if not found it asks for then looks for last names, when it finds one it asks if it should look for another. This all works great, to improve I would like to have it insert in ABC order the name if it is not already in the list. I have last names in column A and First Name MI in column B.

I can do this manually, but thanks to you guys this is becoming a slick program, and if it can be done, why not?

Thanks, and as many of you know I am dense, so please be kind.

 

[SIXTH SENSE]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

The easy way would be to insert at the bottom and then sort BUT I don't think this will work because:
I have 55 sheets the last of which totals for all the others.
I need to keep the Names in the same row in each of the 55 sheets.
I search for an account number if not found it asks for then looks for last names, when it finds one it asks if it should look for another. This all works great, to improve I would like to have it insert in ABC order the name if it is not already in the list. I have last names in column A and First Name MI in column B.

I can do this manually, but thanks to you guys this is becoming a slick program, and if it can be done, why not?

Thanks, and as many of you know I am dense, so please be kind.

Hi!
Im confused. You want to keep the name in the same row, but you want to
insert a new name so that it will be in ABC (sorted order).What happened to the other rows? they will be displaced!
if yuo just want them in order, why not just sort it?

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Good Question,

They need to stay in order for all the sheets.
Say I am into the 17th week of the year and need to insert someone
I link all the sheets and insert the row where needed. There will be no information on this row for the first 16 pages, but when I copy the formulars into this row for the 55th sheet all the infomation entered from sheets 17-54 will be summed. At the beginning of each week I copy the names ect to the next sheet. All the previous sheets will have blanks in the rows where info was added up to when it was added.
If you could sort 17 sheets at the same time that would work but I don't think you can do that. And in the 49th week a screw up would be...
Of course I have it backed up.

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Sub InsertName()
[A1].Select
NewName = InputBox("New Name Needed", "Enter the Name")

Range("A3").Select
Do While True
If Mid(NewName, 1, 1) > Mid(ActiveCell.Value, 1, 1) Then ActiveCell.Offset(1, 0).Select

If Mid(NewName, 1, 1) = Mid(ActiveCell.Value, 1, 1) Then NextLetter2
Loop
End

End Sub
Sub NextLetter2()
Do While True
If Mid(NewName, 2, 1) > Mid(ActiveCell.Value, 2, 1) Then ActiveCell.Offset(1, 0).Select

If Mid(NewName, 2, 1) = Mid(ActiveCell.Value, 2, 1) Then NextLetter3
Loop
End
End Sub
Sub NextLetter3()

End Sub

This starts out working at least on the first letter of a name but when it looks for the second letter it doesn't see it.

Any Idea... And got a better way to do this... Must be plenty

Thanks

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

I am not sure if I'm reading this correctly, but this is my understanding:

You want to insert a row into the same place in all 55 sheets. In sheets 1-54 you may or may not put in data, but in sheet 55 you want to total.

If so:
Select all sheets
Insert a row (which will insert it into each sheet in the same spot)
Select the sheet 1-54 that you want to update and do so.
In sheet 55, copy your formulas into the new row.

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

That's right and the way I am doing it now.
What I would like to do is type in a name and have the macro find the row it should go into and do it for me.

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Doesn't the "do while true" allow you to then stop the macro at that point and then have the macro insert the rows for you, something like:

Rownumber = ActiveCell.Row
Sheets(Array("Sheet3", "Sheet2", "Sheet1")).Select
Rows(Rownumber).Select
Selection.Insert Shift:=xlDown

etc

 

[want2b]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Yes that is where I am heading.

I've got a problem with it looking at the second letter.

THe Mid(NewName,2,1) > Mid(ActiveCell.Value,2,1) never becomes true

Where Mid(NewName 1,1)> Mid(ActiveCell.Value,1,1) Does and I can't
see the difference???
I changed from Left(NewName,1) and it still worked so why doesn't the 2,1 Work?

Could it be something with case? I tried using PROPER, Maybe UPPER
but am not sure how to use it in the way I am trying.
Thanks

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

I haven't tested it, but perhaps you need to use activecell.text not activecell.value.

 

[tactps]

Re: I'm Back --- New Stand Alone Question -Insert Name in Li

Why have you got an end sub in the middle. I would have thought that this should be one macro.