title: Excel VBA button. Copy rows from Sheet1 to Sheet2 / Condition: column value
body: <p>I'm using code below to copy rows from Sheet1 to Sheet2.
  I have 3 questions about.</p>
  <ol>
  <li>Why this function always copy row A2? Even if value is &quot;0&quot;.</li>
  <li>How to copy just value, no formatting?</li>
  <li>Is it possible to skip column B when copy? &quot;C&quot; from Sheet1 will be &quot;B&quot; in Sheet2, etc.</li>
  </ol>
  <blockquote>
  <pre><code>Sub COPY_SA()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim rng As Range, rngToCopy As Range
  Dim lastrow As Long
  'change Sheets to suit
  Set ws1 = ThisWorkbook.Worksheets(&quot;SA&quot;)
  Set ws2 = ThisWorkbook.Worksheets(&quot;JC_input&quot;)
  
  With ws1
      'assumung that your data stored in column A:D, Sheet1
      lastrow = .Cells(.Rows.Count, &quot;A&quot;).End(xlUp).Row
      Set rng = .Range(&quot;A2:D&quot; &amp; lastrow)
      'clear all filters
      .AutoFilterMode = False
      With rng
          'apply filter
          .AutoFilter Field:=4, Criteria1:=&quot;&gt;0&quot;
          On Error Resume Next
          'get only visible rows
          Set rngToCopy = .SpecialCells(xlCellTypeVisible)
          On Error GoTo 0
      End With
      'copy range
      If Not rngToCopy Is Nothing Then rngToCopy.CopyDestination:=ws2.Range(&quot;A2&quot;)
      'clear all filters
      .AutoFilterMode = False
  End With
  Application.CutCopyMode = False End Sub
  </code></pre>
  </blockquote>
  <p>I manage to modify like below. Still have issue with range in Worksheet &quot;ws1&quot;. Cannot set filter in second row and copy range from row 3. That is why added: &quot;ws2.Rows(3).Delete&quot;. Code always copy row 1.
  Row 1 got some comments.
  Row 2 got column names.</p>
  <blockquote>
  <pre><code>Sub COPY_SA()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim rng As Range, rngToCopy As Range
  Dim lastrow As Long
  'change Sheets to suit
  Set ws1 = ThisWorkbook.Worksheets(&quot;SA&quot;)
  Set ws2 = ThisWorkbook.Worksheets(&quot;JC_input&quot;)
  
  With ws1
      'assumung that data stored in column C:E, Sheet1
      lastrow = .Cells(.Rows.Count, &quot;C&quot;).End(xlUp).Row
      'can not make range from row 3 ???
      Set rng = .Range(&quot;C1:E&quot; &amp; lastrow)
      'clear all filters
      .AutoFilterMode = False
      With rng
          'apply filter with criteria in column 3 of range C:E
          'can not make filter in row 2 ???
          .AutoFilter Field:=3, Criteria1:=&quot;&gt;0&quot;
          On Error Resume Next
          'get only visible rows
          Set rngToCopy = .SpecialCells(xlCellTypeVisible)
          On Error GoTo 0
      End With
      'copy range
      If Not rngToCopy Is Nothing Then rngToCopy.Range(&quot;A:C&quot;).Copy
      'paste from row 3
      ws2.Range(&quot;A3&quot;).PasteSpecial Paste:=xlValues
      'delete no needed row
      ws2.Rows(3).Delete
      'clear all filters
      .AutoFilterMode = False
      End With
  Application.CutCopyMode = False
    If Not ActiveSheet.AutoFilterMode Then
      ws1.Range(&quot;2:2&quot;).AutoFilter
      End If
  End Sub
  </code></pre>
  </blockquote>