Movie Database Search.

Your data should be in one sheet, separated into columns.

Your existing code doesn't even point to the correct cells. Try this

Option Explicit

Private Sub Search_Click()
    Dim rCl As Range
    Dim NR As Long
    Dim sFind As String
    Dim Movie As String

    If IsEmpty(Range("A2")) Then
        MsgBox "Please enter your search"
        Exit Sub
    End If
    'Erase old results
    Sheets("Search").Range("A5:K" & Rows.Count).ClearContents
    'List new results from all sheets
    sFind = Range("A2").Value
    With Worksheets("Movie List1")
        For Each rCl In .UsedRange.Columns(1).Offset(1, 0).Cells
            Movie = rCl.Value
            If InStr(Movie, sFind) > 0 Then
                With Sheets("Search")
                    NR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                    .Cells(NR, 1).Value = rCl.Value
                    .Cells(NR, 2).Value = rCl.Offset(1, 0)
                    .Cells(NR, 3).Value = rCl.Offset(2, 0)
                    'etc
                End With
            End If
        Next rCl
    End With
End Sub

I think this fixes it. I changed the Row reference instead of the Column in the database part

Option Explicit
Private Sub Search_Click()

    Dim rCl As Range
    Dim NR As Long
    Dim sFind As String
    Dim Movie As String

    If IsEmpty(Range("A2")) Then
        MsgBox "You haven't enter anything, please enter your search."
        Exit Sub
    End If
    'Erase old results
    Sheets("Search").Range("A5:K" & Rows.Count).ClearContents
    'List new results from all sheets
    sFind = Range("A2").Value
    With Worksheets("Movie List1")
        For Each rCl In .UsedRange.Columns(1).Offset(1, 0).Cells
            Movie = rCl.Value
            If InStr(Movie, sFind) > 0 Then
                With Sheets("Search")
                    NR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                    .Cells(NR, 1).Value = rCl.Value
                    .Cells(NR, 2).Value = rCl.Offset(, 1)    'for size
                    .Cells(NR, 3).Value = rCl.Offset(, 2)    'for album name
                    .Cells(NR, 4).Value = rCl.Offset(, 3)    'for years
                    .Cells(NR, 5).Value = rCl.Offset(, 5)    'for catogary
                    .Cells(NR, 6).Value = rCl.Offset(, 6)    'for Season
                    .Cells(NR, 7).Value = rCl.Offset(, 7)    'for Type
                    .Cells(NR, 8).Value = rCl.Offset(, 8)    'for format
                    .Cells(NR, 9).Value = rCl.Offset(, 9)    ' for length
                    .Cells(NR, 10).Value = rCl.Offset(, 4)    ' for length
                End With
            End If
        Next rCl
    End With
End Sub