VBA: runtime error 1004 : Method range of object - 'Global' failed

[kata___]

Hello everyone, I have this error in my code (red part). I guess it´s a just a little mistake, but I can´t find it. Maybe someone can help me.

It is made on CommandButton for sort by two keys and I remake it from my professor's code (and change name of lists ect.) and insert some condions, but it doesn't work.

Where cmbx_klic1 is ComboBox for first key and cmbx_klic2 is ComboBox for second key for names by what you can sort it. And optb_vzest_1 is OptionButton for ascending for first key and optb_vzest_2 for ascending for second key. The last is check_razeni - CheckBox for not haveing one key for sorting, but two keys. And the list have really the same name as the part of the list.

Here the Code so far:

Private Sub cmdb_razeni_Click()

Dim smer1 As Byte, smer2 As Byte

    If optb_vzest_1 Then

        smer1 = xlAscending

    Else

        smer1 = xlDescending

    End If

     If optb_vzest_2 Then

            smer2 = xlAscending

        Else

            smer2 = xlDescending

        End If

    Sheets("data").Select

    Range("C6").Select

    ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear

    If check_razeni = False Then

        ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        Range(cmbx_klic1).Select

    Else

       ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear

       ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic1), _

        SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal

        ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic2), _

        SortOn:=xlSortOnValues, Order:=smer2, DataOption:=xlSortNormal

    End If

If cmbx_klic1 = cmbx_klic2 Then

        MsgBox "Oba zadané klíče jsou shodné. Zvolte odlišné.": Exit Sub

End If

   With ActiveWorkbook.Worksheets("data").Sort

        .SetRange Range("data")

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range(cmbx_klic1).Select

    frm_menu.Hide

End Sub

Thanks a lot to everyone, who will try to help me.

[#2]

@kata___ 

Range(cmbx_klic1) refers to the active worksheet. If data is not the active sheet when you run the code, this causes a discrepancy. So try changing Range(cmbx_klic1) to Worksheets("data").Range(cmbx_klic1) and similar for Range(cmbx_klic2)

[#3]

@Hans Vogelaar 

Thank you, but now there is Run-time error '1004': Application-defined or object-defined error in the same area. Do you possibly have any other ideas where the mistake could be?

[#4]

@kata___ 

Does this work?

Private Sub cmdb_razeni_Click()
    Dim w As Worksheet
    Dim smer1 As Byte, smer2 As Byte
    If cmbx_klic1 = cmbx_klic2 Then
        MsgBox "Oba zadané klí?e jsou shodné. Zvolte odlišné."
        Exit Sub
    End If
    If optb_vzest_1 Then
        smer1 = xlAscending
    Else
        smer1 = xlDescending
    End If
    If optb_vzest_2 Then
        smer2 = xlAscending
    Else
        smer2 = xlDescending
    End If
    Set w = Worksheets("data")
    With w.Range("data")
        If check_razeni = False Then
            .Sort Key1:=w.Range(cmbx_klic1), Order1:=smer1, Header:=xlYes
        Else
            .Sort Key1:=w.Range(cmbx_klic1), Order1:=smer1, _
                Key2:=w.Range(cmbx_klic2), Order2:=smer2, Header:=xlYes
        End If
    End With
    frm_menu.Hide
End Sub

[#5]

@Hans Vogelaar 

No, it doesn´t work, there is still Run-time error '1004': Method 'Range' of object'_Worksheet' failed.

Can you think of anything else?

[#6]

@kata___ 

I'd have to see a copy of the workbook.

[#7]

I am getting same error in my below code. Can someone help on priority?? 

Sub Extract_Output()

Windows("Mailer - 05-06-21.xlsm").Activate
Sheets("Sheet2").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

'Option Explicit

Dim cell As Range
Dim Table As Range

Dim cutpath As String

cutpath = ActiveWorkbook.path & "\"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In Range("RM")
[valRM] = cell.Value

Range("a1:ah20000").AdvancedFilter Action:=xlFilterCopy, criteriarange:=Range("Criteria"), CopyToRange:=Range("Extract"), Unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
Workbooks.Add