Copy range and paste to next empty row in a defined row or area

I've been looking in various forums to help with this however there isn't anything exactly what I need.

I have a report that I need users to enter information into, cells b3:f3. once all the data is added I need them to click a button to add that information to a report that is contained on the dame sheet. The tricky part is I need the macro to copy b3:f3 and paste it into the next empty row, however it needs to be within a range depending on what "Typ" is entered. In the example book attached, if the type selected was "General" I want the data to be copied and pasted into the "General" or rows 8:17. If the type is Specialist then it needs to be copied to the Specialist section or rows 20:29.

I am trying to update a report that was created by someone else however they are no longer contactable and the book they created where this macro works is password protected so I cannot view their code to see how it was done.

Thanks for any help, and please contact me if you need more of a description of what I am trying to achieve.

Hi JimmyG

Will you ALWAYS have ONLY 10 Items under General and under Specialist?

Hi JimmyG

Try this Code in the attached

Option Explicit

Private Sub Workbook_Open()

    'set protection using UserInterface to allow macros to work
    With Sheets("Sheet1")
        .Protect _
                DrawingObjects:=True, _
                Contents:=True, _
                Scenarios:=True, _
                UserInterfaceOnly:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub

and

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Dim NR As Long
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$C$4" Then
        For Each cel In Range("B3:F3")
            If cel.Value = "" Then
                MsgBox "Missing Data"
                Range(cel.Address).Activate
                Application.EnableEvents = False
                Range("C4").Value = ""
                Application.EnableEvents = True
                Exit Sub
            End If
        Next cel

        Select Case Target.Value
        Case "General"
            With Range("General")
                NR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row + 1
                Application.EnableEvents = False
                Cells(NR, "B").Resize(1, 5).Value = Range("B3").Resize(1, 5).Value
                Application.EnableEvents = True
            End With
        Case "Specialist"
            With Range("Specialist")
               NR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row + 1
                Application.EnableEvents = False
                Cells(NR, "B").Resize(1, 5).Value = Range("B3").Resize(1, 5).Value
                Application.EnableEvents = True
            End With
        End Select

    End If
End Sub

Please notice the Named Ranges "General" and "Specialist"

The Code assumes this as "Yes"

Will you ALWAYS have ONLY 10 Items under General and under Specialist

Hi JimmyG

The Code in the Previous File will run when Cell C4 is changed...no Button required.

The Code in this attachment runs from a Button, as requested.

Please note, without the Named Ranges the Code cannot and will not run.

Please notice the Named Ranges "General" and "Specialist"

Hi James

As you've marked this Thread as "Solved" it would be in your best interest to start a new Thread for these new requirements.
If you start a new Thread and, if you wish, you can PM me with a Link to your new Thread...I'll look at it.