Dzień dobry.

W jednej zakładce mam dwie tabele, które powstają na podstawie importowanych danych. Pierwsza tabela ma dwie kolumny: Area I TTYY, w drugiej tabeli jest tylko Area (to co w tabeli nr 1 ale bez powtórzeń). W następnej zakładce mam w komórce C8 listę, która powstaje z Area, a w komórce C9 chcę mieć listę wyboru TTYY, ale tylko dla odpowiedniej Area, którą wybrałem w komórce A8. 

W załączniku jest plik z makrem, który czasami wywala błąd 1004:Application-defined or object-defined error.

Dla danych z zakładki DATA działa, ale jeżeli podmienimy je na dane z NOWE (kol. A:B), to już wywala błąd.

Niestety nie mogę wgrać pliku, poniżej kod makra:

Sub UpdateArea()


Dim fNameAndPath As Variant, wb As Workbook
Dim LastRow As Integer
Dim twb As Workbook

 
Application.DisplayStatusBar = True
Application.ScreenUpdating = False

'enter your message for the statusbar:
Application.StatusBar = "Now processing...."
      
Set twb = ThisWorkbook
Set wb = ThisWorkbook
    
      
   
                                    For Each tbl In Sheets("Data").ListObjects
                                        tbl.Unlist
                                    Next
  
  
  If wb.Sheets("DATA").Range("A2") <> "" Then


    'Validation lists ASM part (CREATE DEPENDENT VALIDATION LISTS)
    With wb.Sheets("DATA")
        .Rows("1:1").Font.Bold = True
        .Rows("1:1").HorizontalAlignment = xlCenter
        .Columns("A:B").EntireColumn.AutoFit
        .Columns("A:A").Copy
        .Columns("E:E").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range("E:E").RemoveDuplicates Columns:=1, Header:=xlYes
        .Columns("E:E").EntireColumn.AutoFit
        'Format range with Area Names as Table
            .ListObjects.Add(xlSrcRange, .Range("E1").CurrentRegion, , xlYes).Name = "Table1"
       
        
            
       ' .Range("Table1[[#All],[Area Name]]").Select
            .ListObjects("Table1").TableStyle = "TableStyleLight8"
         
            .ListObjects("Table1").Name = "tbl_primary"
         'Sort data
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("E1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .Sort.SetRange Range("E:E")
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
        .Select
    End With


'Name range with Area Names
 wb.Names.Add Name:="dd_primary", RefersToR1C1:="=tbl_primary[Area Name]"
 wb.Names("dd_primary").Comment = ""
 
 'Add validation list to New Area field
    With wb.Sheets("NEW SR").Range("C8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=dd_primary"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
 
 
         
 'Format range with Area Names and Territory Names and Job Code as Table and sort the data
 'ascending by Area Name
    With wb.Sheets("DATA")
        .Select
            .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Table2"
        
            .Range("Table2[#All]").Select
            .ListObjects("Table2").TableStyle = "TableStyleLight8"
      
            .ListObjects("Table2").Name = "tbl_secondary"
       
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("tbl_secondary[[#All],[Area Name]]"), SortOn:= _
                xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .Sort.SetRange Range("A:C")
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
 
 
 wb.Sheets("NEW SR").Select
    
 'Create Name with formula which identify Territory List based on Area Name
    'wb.Names.Add Name:="dd_ttyy", RefersToR1C1:="=tbl_secondary[#All]"
    wb.Names.Add Name:="dd_ttyy", RefersToR1C1:= _
        "=INDEX(tbl_secondary[Territory Name],MATCH('NEW SR'!R8C3,tbl_secondary[Area Name],0),1): INDEX(tbl_secondary[Territory Name],MATCH('NEW SR'!R8C3,tbl_secondary[Area Name],1),1)"
    wb.Names("dd_ttyy").Comment = ""
   

'Add validation list to Current ttyy field in NEW SR
    With wb.Sheets("NEW SR").Range("C9").Validation
     
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=dd_ttyy"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With



'Remove temporary selected Area Name
    wb.Sheets("NEW SR").Range("C8") = ""
    wb.Sheets("NEW SR").Range("C9") = ""
    
    
    End If
    
 
Application.StatusBar = "Completed!"

Application.StatusBar = False


Application.StatusBar = False
Application.ScreenUpdating = True
ActiveWindow.ScrollRow = 1


MsgBox "AREA List tab has been updated!"
End Sub

Dziękuję za pomoc.