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

%3CLINGO-SUB%20id%3D%22lingo-sub-2108156%22%20slang%3D%22en-US%22%3EVBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108156%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20I%20have%20this%20error%20in%20my%20code%20(red%20part).%20I%20guess%20it%C2%B4s%20a%20just%20a%20little%20mistake%2C%20but%20I%20can%C2%B4t%20find%20it.%20Maybe%20someone%20can%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20made%20on%20CommandButton%20for%20sort%20by%20two%20keys%20and%20I%20remake%20it%20from%20my%20professor's%20code%20(and%20change%20name%20of%20lists%20ect.)%20and%20insert%20some%20condions%2C%20but%20it%20doesn't%20work.%3C%2FP%3E%3CP%3EWhere%20cmbx_klic1%20is%20ComboBox%20for%20first%20key%20and%20cmbx_klic2%20is%20ComboBox%20for%20second%20key%20for%20names%20by%20what%20you%20can%20sort%20it.%20And%20optb_vzest_1%20is%20OptionButton%20for%20ascending%20for%20first%20key%20and%20optb_vzest_2%20for%20ascending%20for%20second%20key.%20The%20last%20is%20check_razeni%20-%20CheckBox%20for%20not%20haveing%20one%20key%20for%20sorting%2C%20but%20two%20keys.%20And%20the%20list%20have%20really%20the%20same%20name%20as%20the%20part%20of%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20the%20Code%20so%20far%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdb_razeni_Click()%3C%2FP%3E%3CP%3EDim%20smer1%20As%20Byte%2C%20smer2%20As%20Byte%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20optb_vzest_1%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20smer1%20%3D%20xlAscending%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20smer1%20%3D%20xlDescending%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3BIf%20optb_vzest_2%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20smer2%20%3D%20xlAscending%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20smer2%20%3D%20xlDescending%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheets(%22data%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22C6%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWorkbook.Worksheets(%22data%22).Sort.SortFields.Clear%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20check_razeni%20%3D%20False%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWorkbook.Worksheets(%22data%22).Sort.SortFields.Add%20Key%3A%3DRange(cmbx_klic1)%2C%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3Dsmer1%2C%20DataOption%3A%3DxlSortNormal%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(cmbx_klic1).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20ActiveWorkbook.Worksheets(%22data%22).Sort.SortFields.Clear%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%20ActiveWorkbook.Worksheets(%22data%22).Sort.SortFields.Add%20Key%3A%3DRange(cmbx_klic1)%2C%20_%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3Dsmer1%2C%20DataOption%3A%3DxlSortNormal%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWorkbook.Worksheets(%22data%22).Sort.SortFields.Add%20Key%3A%3DRange(cmbx_klic2)%2C%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3Dsmer2%2C%20DataOption%3A%3DxlSortNormal%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3EIf%20cmbx_klic1%20%3D%20cmbx_klic2%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MsgBox%20%22Oba%20zadan%C3%A9%20kl%C3%AD%C4%8De%20jsou%20shodn%C3%A9.%20Zvolte%20odli%C5%A1n%C3%A9.%22%3A%20Exit%20Sub%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20With%20ActiveWorkbook.Worksheets(%22data%22).Sort%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.SetRange%20Range(%22data%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Header%20%3D%20xlYes%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MatchCase%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Orientation%20%3D%20xlTopToBottom%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.SortMethod%20%3D%20xlPinYin%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Apply%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(cmbx_klic1).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20frm_menu.Hide%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20to%20everyone%2C%20who%20will%20try%20to%20help%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2108156%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109482%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947474%22%20target%3D%22_blank%22%3E%40kata___%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20have%20to%20see%20a%20copy%20of%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2109447%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20it%20doesn%C2%B4t%20work%2C%20there%20is%20still%20%3CFONT%20color%3D%22%23008000%22%3ERun-time%20error%20'1004'%3A%20Method%20'Range'%20of%20object'_Worksheet'%20failed%3CFONT%20color%3D%22%23000000%22%3E.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3ECan%20you%20think%20of%20anything%20else%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2108588%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108588%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947474%22%20target%3D%22_blank%22%3E%40kata___%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20work%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20cmdb_razeni_Click()%0A%20%20%20%20Dim%20w%20As%20Worksheet%0A%20%20%20%20Dim%20smer1%20As%20Byte%2C%20smer2%20As%20Byte%0A%20%20%20%20If%20cmbx_klic1%20%3D%20cmbx_klic2%20Then%0A%20%20%20%20%20%20%20%20MsgBox%20%22Oba%20zadan%C3%A9%20kl%C3%AD%3Fe%20jsou%20shodn%C3%A9.%20Zvolte%20odli%C5%A1n%C3%A9.%22%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20If%20optb_vzest_1%20Then%0A%20%20%20%20%20%20%20%20smer1%20%3D%20xlAscending%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20smer1%20%3D%20xlDescending%0A%20%20%20%20End%20If%0A%20%20%20%20If%20optb_vzest_2%20Then%0A%20%20%20%20%20%20%20%20smer2%20%3D%20xlAscending%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20smer2%20%3D%20xlDescending%0A%20%20%20%20End%20If%0A%20%20%20%20Set%20w%20%3D%20Worksheets(%22data%22)%0A%20%20%20%20With%20w.Range(%22data%22)%0A%20%20%20%20%20%20%20%20If%20check_razeni%20%3D%20False%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Sort%20Key1%3A%3Dw.Range(cmbx_klic1)%2C%20Order1%3A%3Dsmer1%2C%20Header%3A%3DxlYes%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20.Sort%20Key1%3A%3Dw.Range(cmbx_klic1)%2C%20Order1%3A%3Dsmer1%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Key2%3A%3Dw.Range(cmbx_klic2)%2C%20Order2%3A%3Dsmer2%2C%20Header%3A%3DxlYes%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20frm_menu.Hide%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2108566%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20but%20now%20there%20is%20%3CFONT%20color%3D%22%23008000%22%3ERun-time%20error%20'1004'%3A%20Application-defined%20or%20object-defined%20error%3C%2FFONT%3E%20in%20the%20same%20area.%20Do%20you%20possibly%20have%20any%20other%20ideas%20where%20the%20mistake%20could%20be%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2108272%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947474%22%20target%3D%22_blank%22%3E%40kata___%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERange(cmbx_klic1)%20refers%20to%20the%20active%20worksheet.%20If%20data%20is%20not%20the%20active%20sheet%20when%20you%20run%20the%20code%2C%20this%20causes%20a%20discrepancy.%20So%20try%20changing%20Range(cmbx_klic1)%20to%20Worksheets(%22data%22).Range(cmbx_klic1)%20and%20similar%20for%20Range(cmbx_klic2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536937%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3A%20runtime%20error%201004%20%3A%20Method%20range%20of%20object%20-%20'Global'%20failed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536937%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20getting%20same%20error%20in%20my%20below%20code.%20Can%20someone%20help%20on%20priority%3F%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20Extract_Output()%3CBR%20%2F%3E%3CBR%20%2F%3EWindows(%22Mailer%20-%2005-06-21.xlsm%22).Activate%3CBR%20%2F%3ESheets(%22Sheet2%22).Select%3CBR%20%2F%3ERange(%22A1%22).Select%3CBR%20%2F%3ESelection.ListObject.QueryTable.Refresh%20BackgroundQuery%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3E'Option%20Explicit%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20cell%20As%20Range%3CBR%20%2F%3EDim%20Table%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20cutpath%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3Ecutpath%20%3D%20ActiveWorkbook.path%20%26amp%3B%20%22%5C%22%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEM%3E%3CSTRONG%3EFor%20Each%20cell%20In%20Range(%22RM%22)%3C%2FSTRONG%3E%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E%3CSTRONG%3E%5BvalRM%5D%20%3D%20cell.Value%3C%2FSTRONG%3E%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22a1%3Aah20000%22).AdvancedFilter%20Action%3A%3DxlFilterCopy%2C%20criteriarange%3A%3DRange(%22Criteria%22)%2C%20CopyToRange%3A%3DRange(%22Extract%22)%2C%20Unique%3A%3DFalse%3CBR%20%2F%3ERange(Range(%22Extract%22)%2C%20Range(%22Extract%22).End(xlDown)).Copy%3CBR%20%2F%3EWorkbooks.Add%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

6 Replies

@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)

 

 

@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?

@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

@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?

@kata___ 

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

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