Thread: Assigning information to a specific place based on drop down box selection

I need a solution that is more simple than this so anyone can use the spreadsheet. I thought perhaps there is another way around it apart from using the find function?

The solution can be as simple as pressing a button which has a macro assigned to it. In order to get a precise answer to your need upload a sample workbook that meets the following criteria:

1. It EXACTLY duplicates the structure in your real workbook, AND
2. It contains representative but non-sensitive data, AND
3. It shows an example or two of the desired results

This works with the sample workbook you provide (drop down down doesn't work because of external links). See attached.

Option Explicit

Sub AddNewEntry()

    Dim lrow As Long, strFind As String, rngFound As Range, rngCopy As Range
    
    lrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    strFind = Sheet1.Range("A2").Text
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    On Error Resume Next
    
    With Sheet1
    
        Set rngCopy = .Range("A3:H3")
    
        Set rngFound = .Columns(1).Find(What:=strFind, after:=.Cells(5, 1), LookIn:=xlValues, _
            Lookat:=xlWhole, Searchorder:=xlByRows, Searchdirection:=xlNext)
        
        On Error GoTo err:
        
        If Not rngFound Is Nothing Then
            rngFound.Offset(1, 0).EntireRow.Insert
            rngCopy.Copy
            rngFound.Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            rngFound.Offset(1, 0).EntireRow.Font.Bold = False
            Application.CutCopyMode = False
            With rngFound
                .CurrentRegion.Sort Key1:=rngFound, order1:=xlAscending, Header:=xlYes
            End With
        Else
            MsgBox "Could not find category"
            Exit Sub
        End If
    End With
    
err:
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

@ sujitshukla

Can you attach the file in 2003 format please.

Download and install the Office 2007 Conversion Pack and you can open 2007 version files (though 2007 Pivot tables will be disabled. Search Microsoft for the coversion pack.

I have kept everything exactly the same as your worksheet, I don't understand what the problem is. The only difference is I have other macros and worksheets in my workbook.

Two possibilities I see are:

1. You don't have a Sheet1 (in my code Sheet1 refers to the sheet code name not the worksheet name)

2. The Dim lrow part of the code is missing rendering the line of code as having an undeclared variable . This is unlikely if you used the code as given.