Problems when filtering columns

[saltire1963]

I have a workbook where one of the sheets (Data) contains many rows and a few columns which multiple co-workers need to access to add new rows and also do searches by filtering. To prevent messing with the underlying data, I've created a button on the ribbon that copies the sheet Data to the end sheet (called Temp) so playing around in Temp does not affect the sheet Data. What I didn't realise though is that if I filter the data in Temp, then add some new rows later in sheet Data, when I create a new sheet Temp and filter it, it does not take into account the new rows added since the original filter was done. So I must have done the original filter when there were 254 rows in Data, there are now 278, but any subsequent filter in Temp ignores any row beyond 254. The help I've found through google searches says just Clear Filter, then reapply new filter to cover all the latest rows. However my workbook does not allow this to be done, I'm still stuck with only being able to filter down to row 254. Looking for other options here if anyone can suggest a way forward pls. Maybe I should add that when I click button I've created on the ribbon and the Temp sheet is created, a userform is displayed with a few option buttons where I can choose which column I want the filter to be done on, so Temp sheet has a few deleted columns compared to Data sheet. Apologies for the long winded explanation. Phew

 

[mole999]

what is the code from your ribbon button

 

[saltire1963]

The code from the ribbon button to the userform is

Sub rxIRibbonUI_******(ribbon As IRibbonUI)
    Set grxIRibbonUI = ribbon
End Sub

Sub rxbtnOpenFrmReports_Click(control As IRibbonControl)
    Sheets("Lessons").Activate
    Range("A2").Select
    frmReports.Show
End Sub

if you meant the code from the userform button to the copying of sheet Data, then this is below

Private Sub cmdCreateReport_Click()

    'Create new temporary sheet that can be worked on
    Sheets("Lessons").Copy After:=Worksheets(Worksheets.Count)
    Application.CutCopyMode = False

    'Choose the criteria to search on depending on Option Button chosen
    With ActiveWorksheet
        If optLocation Then
            Range("B:N").EntireColumn.Delete
        ElseIf optPriority Then
            Range("A:A").EntireColumn.Delete
            Range("B:M").EntireColumn.Delete
        ElseIf optManufacturer Then
            Range("A:F").EntireColumn.Delete
            Range("B:H").EntireColumn.Delete
        ElseIf optActivity Then
            Range("A:G").EntireColumn.Delete
            Range("B:G").EntireColumn.Delete
        ElseIf optNPT Then
            Range("A:H").EntireColumn.Delete
            Range("B:F").EntireColumn.Delete
        ElseIf optOwner Then
            Range("A:I").EntireColumn.Delete
            Range("B:E").EntireColumn.Delete
        ElseIf optStatus Then
            Range("A:L").EntireColumn.Delete
            Range("B:B").EntireColumn.Delete
        ElseIf optHighLevel Then
            Range("A:M").EntireColumn.Delete
        End If
    End With
    
    On Error Resume Next
    ' Name Temp Report sheet, for example Temp Manufacturer
    ActiveSheet.Name = "Temp " & Range("A1")
    
    frmReports.Hide