How to insert a text field into appropriate worksheet based on alphabetical value?

[genepaton]

Hi Guys,

Im trying to make a simple macro for cataloging some equipment and where its located.
I've created a user form where there is a free text field for Equipment. Instead of just inserting this entry into the first available cell, i would like for it to place it in the first available cell under the corresponding first letter of the equipment to the named worksheets A-Z.

(sorry if that isn't too clear)

I have named the worksheets A through to Z, and upon entering the free text 'Equipment' name, say for instance Trolley, for it to recognise the T, open the worksheet named T and insert the equipment name and location etc across the first free column.

Any help greatly appreciated.

 

[genepaton]

I've attached what i've reversed engineered so far, but i don't know if im on the right track.

The following is supposed to place the entry into worksheet A and then moves it the appropriate sheet (but it aint working)

In the Workbook Module

Sub Button2_Click()
UserForm1.Show
End Sub

Sub MoveToNewSheet(ByVal strSrceSheet As String, ByVal lngRow As Long, _
    ByVal strStatus As String)
     
    Select Case strStatus
    Case "A*": strDestSheet = "A"
    Case "B*": strDestSheet = "B"
    Case "C*": strDestSheet = "C"
    Case "D*": strDestSheet = "D"
    Case "E*": strDestSheet = "E"
    Case "F*": strDestSheet = "F"
    Case "G*": strDestSheet = "G"
    Case "H*": strDestSheet = "H"
    Case "I*": strDestSheet = "I"
    Case "J*": strDestSheet = "J"
    Case "K*": strDestSheet = "K"
    Case "L*": strDestSheet = "L"
    Case "M*": strDestSheet = "M"
    Case "N*": strDestSheet = "N"
    Case "O*": strDestSheet = "O"
    Case "P*": strDestSheet = "P"
    Case "Q*": strDestSheet = "Q"
    Case "R*": strDestSheet = "R"
    Case "S*": strDestSheet = "S"
    Case "T*": strDestSheet = "T"
    Case "U*": strDestSheet = "U"
    Case "V*": strDestSheet = "V"
    Case "W*": strDestSheet = "W"
    Case "X*": strDestSheet = "X"
    Case "Y*": strDestSheet = "Y"
    Case "Z*": strDestSheet = "Z"
         
    End Select
     
    With Worksheets(strDestSheet)
        Worksheets(strSrceSheet).Range("A" & lngRow & ":G" & lngRow).Copy _
        Destination:=.Range("A" & .Range("A65536").End(xlUp).Row + 1)
    End With
    Worksheets(strSrceSheet).Rows(lngRow).Delete
End Sub

On each subsequent worksheets code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDestSheet As String
    If Target.Column <> 1 Then Exit Sub
     
    MoveToNewSheet ActiveSheet.Name, Target.Row, Target.Value
End Sub

And the userform code

Private Sub Cancel_Click()
    Unload Me
End Sub

Private Sub Clear_Click()
    Call UserForm_Initialize
End Sub

Private Sub OK_Click()
    ActiveWorkbook.Sheets("A").Activate
    Range("A2").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = TextBox1.Value
    ActiveCell.Offset(0, 1) = TextBox2.Value
    ActiveCell.Offset(0, 2) = TextBox3.Value
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox1.SetFocus
End Sub

 

[mikerickson]

Select case doen't recognize wild cards, one has to incorporate Like into it for that.
Try

strDestSheet = UCase(Left(strStatus, 1))

or

Private Sub OK_Click()
    On Error Goto ErrorOut
    With ActiveWorkbook.Sheets(UCase(Left(TextBox1.Text, 1)))
        With .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Value = TextBox1.Text
            .Offset(0, 1).Value = TextBox2.Text
            .Offset(0, 2).Value = TextBox3.Text
        End With
    End With
    Unload Me
ErrorOut:
    On Error Goto 0
End Sub

 

[mikerickson]

cross post
http://www.ozgrid.com/forum/showthread.php?t=158920

http://www.excelguru.ca/node/7

 

[genepaton]

Hi Mikerickson,

Thank you for your post and pointing out about cross posts to me. I didn't realise but the explanation makes some good points. I'll be sure to include links in future.

Your code works brilliant and i didn't even need all those Select cases, i just used the Command OK click that you suggested.

Thanks for your help!