title: Sometimes pastespecial method of range failes 1004 Error
body: <p>i need you help! So basically what i am trying to do is to copy a filtered range from one workbook (wbimport) into my Active Workbook (wsData). I do it two times. One time with the actual date (szToday) and the date1, because my wbimport file has the date as ending and i need a comparison of the data from both dates.</p>
  <p>As mentioned above i get the error pastespecial method of range failes (1004 Error). I know, that there are many oher posts regarding the same error, but i couldnt find any help. I spend so much time and still couldnt figure it out.</p>
  <p>The error happens in:</p>
  <p>wsData.Range(&quot;H1&quot;).PasteSpecial xlPasteValues</p>
  <p>Thanks in advance!</p>
  <pre><code>Sub ImportDat()
   
      Application.DisplayAlerts = False
      Sheets(4).Delete
      Sheets(4).Delete
      Application.DisplayAlerts = True
      
      Application.ScreenUpdating = False
      
      szToday = Format(Date, &quot;dd-mm-yy&quot;)
      Dim d1 As String
      d1 = Range(&quot;b7&quot;).Value2
      date1 = Format(d1, &quot;dd-mm-yy&quot;)
      
      Dim Dates(1 To 2) As String
      
      Dates(2) = szToday
      Dates(1) = date1
      
      Dim i As Variant
      
      For Each i In Dates()
  
  
      Dim App As New Excel.Application 'create a new (hidden) Excel
      
      
      Sheets.Add After:=ActiveSheet
      ActiveSheet.Name = i
      
      ' remember active sheet
      Dim wsData As Worksheet
      Set wsData = ThisWorkbook.Sheets(i)
      
      wsData.Cells.ClearContents
  
      ' open the import workbook in new Excel (as read only)
      Dim wbImport As Workbook
      Dim FileN As String
      FileN = &quot;***&quot; &amp; Format(i, &quot;YYYY-MM-DD&quot;) &amp; &quot;.xlsb&quot;
      Set wbImport = App.Workbooks.Open(Filename:=FileN, UpdateLinks:=True, ReadOnly:=True)
      
      'wbImport.Worksheets(&quot;Data&quot;).Activate'
      
      'Array für PH1 Bedingungen'
      
      Dim lngCriteriaCountPH1 As Long
      Dim arrCriteriaPH1() As String
      
      lngCriteriaCountPH1 = 6
      
      ReDim arrCriteriaPH1(0 To lngCriteriaCountPH1 - 1)
      
      arrCriteriaPH1(0) = &quot;Commercial All-In-One&quot;
      arrCriteriaPH1(1) = &quot;Commercial Desktop&quot;
      arrCriteriaPH1(2) = &quot;Commercial Notebook&quot;
      arrCriteriaPH1(3) = &quot;Commercial Tablet&quot;
      arrCriteriaPH1(4) = &quot;Visuals&quot;
      arrCriteriaPH1(5) = &quot;Workstation&quot;
      
      'Array für Distributoren'
      
      Dim lngCriteriaCountCN As Long
      Dim arrCriteriaCN() As String
      
      lngCriteriaCountCN = 7
      
      ReDim arrCriteriaCN(0 To lngCriteriaCountCN - 1)
      
      arrCriteriaCN(0) = &quot;ALSO Deutschland GmbH&quot;
      arrCriteriaCN(1) = &quot;api Computerhandels GmbH&quot;
      arrCriteriaCN(2) = &quot;BYTEC Bodry Technology GmbH&quot;
      arrCriteriaCN(3) = &quot;Ingram Micro GmbH&quot;
      arrCriteriaCN(4) = &quot;Ingram Micro Distribution GmbH&quot;
      arrCriteriaCN(5) = &quot;Tech Data Service GmbH&quot;
      arrCriteriaCN(6) = &quot;Tech Data Service GmbH EURO PLAZA, Gebau&quot;
      
      'Autofilter aktivieren'
      Dim LastRowColumnA As Long
      LastRowColumnA = wbImport.Worksheets(&quot;Data&quot;).Cells(Rows.Count, 1).End(xlUp).Row
      
      Dim LastCol As Long
      LastCol = wbImport.Worksheets(&quot;Data&quot;).Cells(1, Columns.Count).End(xlToLeft).Column
      ColLetter = Split(Cells(1, LastCol).Address, &quot;$&quot;)(1)
   
      Set rngFilterRange = wbImport.Worksheets(&quot;Data&quot;).Range(&quot;A1:&quot; &amp; ColLetter &amp; LastRowColumnA)
      
      rngFilterRange.AutoFilter
      rngFilterRange.AutoFilter Field:=2, Criteria1:=&quot;GAT&quot;, Operator:=xlFilterValues
      rngFilterRange.AutoFilter Field:=7, Criteria1:=arrCriteriaPH1(), Operator:=xlFilterValues
      rngFilterRange.AutoFilter Field:=19, Criteria1:=&quot;Y&quot;, Operator:=xlFilterValues
      rngFilterRange.AutoFilter Field:=39, Criteria1:=arrCriteriaCN(), Operator:=xlFilterValues
         
      'copy the data of the import sheet
      wbImport.Worksheets(&quot;Data&quot;).Range(&quot;A1&quot;).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      wsData.Range(&quot;H1&quot;).PasteSpecial xlPasteValues
    
      Dim LastRowColumnH As Long
      LastRowColumnH = Cells(Rows.Count, 8).End(xlUp).Row
      
      'PH3 Adjusted'
      Range(&quot;G1&quot;).Value = &quot;PH3 Adjusted&quot;
      Range(&quot;G2:G&quot; &amp; LastRowColumnH).Formula = &quot;=IFERROR(VLOOKUP(N2&amp;P2,Filter!O:P,2,0),P2)&quot;
      
      'HLPCLM'
      Range(&quot;F1&quot;).Value = &quot;HLPCLM&quot;
      Range(&quot;F2:F&quot; &amp; LastRowColumnH).Formula = &quot;=CONCAT(Y2,U2)&quot;
      
      'OLDMFG'
      Range(&quot;E1&quot;).Value = &quot;OLD MFG&quot;
      Range(&quot;E2:E&quot; &amp; LastRowColumnH).Formula = &quot;=IFERROR(IF(VLOOKUP(F2,'&quot; &amp; date1 &amp; &quot;'!F:AN,35,0)=0,&quot;&quot;&quot;&quot;,VLOOKUP(F2,'&quot; &amp; date1 &amp; &quot;'!F:AN,35,0)),&quot;&quot;&quot;&quot;)&quot;
      Range(&quot;E2:E&quot; &amp; LastRowColumnH).NumberFormat = &quot;dd.mm.yyyy&quot;
      
      'CHANGES'
      Range(&quot;D1&quot;).Value = &quot;CHANGES&quot;
      Range(&quot;D2:D&quot; &amp; LastRowColumnH).Formula = &quot;=IF(AND(AN128=&quot;&quot;&quot;&quot;,E128&lt;&gt;&quot;&quot;&quot;&quot;),&quot;&quot;Deleted&quot;&quot;,IF(AND(AN2&lt;&gt;&quot;&quot;&quot;&quot;,E2=&quot;&quot;&quot;&quot;),&quot;&quot;New&quot;&quot;,IF(AN2=E2,&quot;&quot;&quot;&quot;,IF(AN2&gt;E2,&quot;&quot;Postponed&quot;&quot;,IF(AN2&lt;E2,&quot;&quot;Forwarded&quot;&quot;,&quot;&quot;-&quot;&quot;)))))&quot;
      
      'DISTRIBUTOR'
      Range(&quot;C1&quot;).Value = &quot;Distributor&quot;
      Range(&quot;C2:C&quot; &amp; LastRowColumnH).Formula = &quot;=IF(AT2=&quot;&quot;ALSO Deutschland GmbH&quot;&quot;,&quot;&quot;ALSO&quot;&quot;,IF(AT2=&quot;&quot;api Computerhandels GmbH&quot;&quot;,&quot;&quot;API&quot;&quot;,IF(AT2=&quot;&quot;Bytec Bodry Technology GmbH&quot;&quot;,&quot;&quot;BYTEC&quot;&quot;,IF(OR(AT2=&quot;&quot;Ingram Micro Distribution GmbH&quot;&quot;,AT2=&quot;&quot;Ingram Micro GmbH&quot;&quot;),&quot;&quot;INGRAM&quot;&quot;,IF(OR(AT2=&quot;&quot;Tech Data Service GmbH&quot;&quot;,AT2=&quot;&quot;Tech Data Service GmbH EURO PLAZA, Gebau&quot;&quot;),&quot;&quot;TECHDATA&quot;&quot;,&quot;&quot;&quot;&quot;)))))&quot;
  
      
      'DEAL PN'
      Range(&quot;B1&quot;).Value = &quot;Deal PN&quot;
      Range(&quot;B2:B&quot; &amp; LastRowColumnH).Formula = &quot;=IFERROR(VLOOKUP(Y2,Filter!S:T,2,0),&quot;&quot;No&quot;&quot;)&quot;
  
      App.CutCopyMode = False 'clear clipboard (prevents asking when wb is closed)
      wbImport.Close SaveChanges:=False 'close wb without saving
      App.Quit 'quit the hidden Excel
      
      Next i
      
      'Replace old dates'
      
      
      Worksheets(&quot;Summary&quot;).Range(&quot;B3&quot;).Value = szToday
      Worksheets(&quot;Summary&quot;).Range(&quot;E3&quot;).Value = date1
      
      
      Sheets(&quot;Overview&quot;).Select
      Cells(1, 1).Select
     Application.ScreenUpdating = True
  End Sub
  </code></pre>