title: Run time error 1004 - MS Office excel can not create or use the data range because it is too complex
body: <p>I am trying to write a code for filtering data with particular criteria and selecting filtered data, copy and pasting visible cells only in different sheet. However, I am getting error "Run time error 1004" stating MS Office excel can not create or use the data range because it is too complex.</p>
  
  <p><a href="https://i.stack.imgur.com/O8H0J.jpg" rel="nofollow noreferrer">enter image description here</a></p>
  
  <p>below is the code that I am using</p>
  
  <pre><code>Set mwb = ActiveWorkbook
  fname = ActiveWorkbook.Name
  pth = path
  period = Sheets("DEF").Range("F18").Value
  ddate = Range("L6").Value
  
  
  Sheets("MacroTOSplit").Select
  blr = Range("C50").End(xlUp).Row
  Rcfield = Range("C1").Value
  For a = 4 To blr Step 1
      Sheets("MacroTOSplit").Select
      If Cells(a, "C").Value &lt;&gt; "" Then
         rc1 = Cells(a, "C").Value
  
          Sheets("XYZ").Select
          Cells.AutoFilter
          If lr &gt;= 2 Then
          Range("B2:B" &amp; lr + 1).EntireRow.Delete
          End If
  
  
  
         Sheets("ABC").Select
  
  
         dlr = lr
         Set datarange = Sheets("ABC").Range(Cells(1, 1), Cells(dlr, "BG"))
         'Filter for each unit and copy the data
         datarange.AutoFilter Field:=Rcfield, Criteria1:=rc1, Operator:=xlFilterValues
         datarange.Range(Cells(2, 1), Cells(dlr, "BG")).SpecialCells(xlCellTypeVisible).Copy Sheets("XYZ").Range("A2")
  </code></pre>
  
  <p>I am getting error at last step.</p>
  
  <p>Please provide some solution for this.</p>
  
  <p>Thanks,
  Ravi</p>