title: VBA macro to copy and paste filtered data to new sheet
body: <p>I am trying to copy filtered data from one sheet to another, but for some reason I get a runtime error 1004 saying &quot;to copy all cells from another worksheet to this worksheet make sure you paste them into the first cell (A1 or R1C1)&quot; I actually don't want the header row copied, so all visible bar that row</p>
  <p>What I am wanting is the copied data to be pasted to the first available row in the target sheet. Here is the code I have which filters for certain things, but then falls over on the paste line</p>
  <pre><code>Sub BBWin()
  '
  ' BB Win Macro
  ' This macro will filter BB Win 1 - 8
  '
      With ActiveSheet.Range(&quot;A1&quot;).CurrentRegion
        With .Resize(, .Columns.Count + 1)
           With .Cells(2, .Columns.Count).Resize(.Rows.Count - 1)
              .FormulaR1C1 = &quot;=if(or(rc7={&quot;&quot;K.BB_Win_1_2019&quot;&quot;,&quot;&quot;K.BB_Win_2_2019&quot;&quot;,&quot;&quot;K.BB_Win_3_2019&quot;&quot;,&quot;&quot;K.BB_Win_4_2019&quot;&quot;,&quot;&quot;K.BB_Win_5_2019&quot;&quot;,&quot;&quot;K.BB_Win_6_2019&quot;&quot;,&quot;&quot;K.BB_Win_7_2019&quot;&quot;,&quot;&quot;K.BB_Win_8_2019&quot;&quot;}),&quot;&quot;X&quot;&quot;,&quot;&quot;&quot;&quot;)&quot;
              .Value = .Value
           End With
           .HorizontalAlignment = xlCenter
        End With
          Cells.Select
          Selection.SpecialCells(xlCellTypeVisible).Copy
          Workbooks(&quot;Predictology-Reports.xlsx&quot;).Sheets(&quot;BB Reports&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      End With
      Application.CutCopyMode = False
  End Sub
  </code></pre>
  <p>Any suggestions as to what is missing to have it work correctly?</p>
  <p>=========================================</p>
  <p>OK, perhaps I should have tried the question another way, posting the original working macro I was supplied, rather than posting my attempt to rewrite it.</p>
  <p>This is basically the same thing as what I posted above, with the formula changed to look for different  text, though it also has autofilter settings (which I don't need) and hides columns (which I don't need to do). This is working perfectly for me and does exactly what it is supposed to. I basically tried to duplicate it and remove the unwanted elements, but as you saw, found the error originally indicated. Obviously my limited knowledge caused the initial issue.</p>
  <pre><code>Sub Low_Risk()
  '
  ' Low Risk Lays Macro
  ' This macro will filter for Remove VDW Rank 1, Class, Distance &lt;=1650, # of Runners &lt;=9, Exclude Brighton, Yarmouth, Windsor &amp; Wolverhampton
  '
      With ActiveSheet.Range(&quot;A1&quot;).CurrentRegion
        With .Resize(, .Columns.Count + 1)
           With .Cells(2, .Columns.Count).Resize(.Rows.Count - 1)
              .FormulaR1C1 = &quot;=if(or(rc8={&quot;&quot;Brighton&quot;&quot;,&quot;&quot;Yarmouth&quot;&quot;,&quot;&quot;Windsor&quot;&quot;,&quot;&quot;Wolverhampton&quot;&quot;}),&quot;&quot;X&quot;&quot;,&quot;&quot;&quot;&quot;)&quot;
              .Value = .Value
           End With
           .AutoFilter Field:=4, Criteria1:=&quot;&lt;=9&quot;
           .AutoFilter Field:=11, Criteria1:=&quot;&lt;=1650&quot;
           .AutoFilter .Columns.Count, &quot;&lt;&gt;X&quot;
           .AutoFilter Field:=29, Criteria1:=&quot;&lt;&gt;1&quot;
           .HorizontalAlignment = xlCenter
        End With
          .Columns(&quot;C:C&quot;).EntireColumn.Hidden = True
          .Columns(&quot;G:G&quot;).EntireColumn.Hidden = True
          .Columns(&quot;I:I&quot;).EntireColumn.Hidden = True
          .Columns(&quot;L:L&quot;).EntireColumn.Hidden = True
          .Columns(&quot;N:W&quot;).EntireColumn.Hidden = True
          .Columns(&quot;Y:AB&quot;).EntireColumn.Hidden = True
          .Columns(&quot;AD:AJ&quot;).EntireColumn.Hidden = True
          .Columns(&quot;AO:AO&quot;).EntireColumn.Hidden = True
          .Columns(&quot;AQ:BQ&quot;).EntireColumn.Hidden = True
          .Columns(&quot;BT:CP&quot;).EntireColumn.Hidden = True
          .Parent.AutoFilter.Range.Offset(1).Copy
          Workbooks(&quot;New Results File.xlsm&quot;).Sheets(&quot;Low Risk Lays&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      End With
      Application.CutCopyMode = False
  End Sub
  </code></pre>
  <p>As indicated, this works absolutely perfectly, nested Withs and all. I can change the original formula so it is looking in the correct column and only for the text I want, but I obviously was not able to successfully remove the autofilter elements and the elements which hide columns without bringing up an error. I assume the removal of the <code>.Parent.AutoFilter.Range.Offset(1).Copy</code> line was the culprit, but wasn't sure how to approach the removal of the unwanted elements.</p>
  <p>This original macro was supplied to me in one of the forums and I am loath to alter the formula part which does a good job of looking for the many text elements required to be copied. That was why I only looked to alter the autofilter section and hidden column section</p>
  <p>I'm not sure if this helps at all, but it may clarify things a little</p>
  <p>cheers and thanks so much for your effort</p>