Excel VBA button. Copy rows from Sheet1 to Sheet2 / Condition: column value

Question

I'm using code below to copy rows from Sheet1 to Sheet2. I have 3 questions about.

Why this function always copy row A2? Even if value is "0".
How to copy just value, no formatting?
Is it possible to skip column B when copy? "C" from Sheet1 will be "B" in Sheet2, etc.

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("SA")
Set ws2 = ThisWorkbook.Worksheets("JC_input")

With ws1
    'assumung that your data stored in column A:D, Sheet1
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A2:D" & lastrow)
    'clear all filters
    .AutoFilterMode = False
    With rng
        'apply filter
        .AutoFilter Field:=4, Criteria1:=">0"
        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("A2")
    'clear all filters
    .AutoFilterMode = False
End With
Application.CutCopyMode = False End Sub

I manage to modify like below. Still have issue with range in Worksheet "ws1". Cannot set filter in second row and copy range from row 3. That is why added: "ws2.Rows(3).Delete". Code always copy row 1. Row 1 got some comments. Row 2 got column names.

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("SA")
Set ws2 = ThisWorkbook.Worksheets("JC_input")

With ws1
    'assumung that data stored in column C:E, Sheet1
    lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
    'can not make range from row 3 ???
    Set rng = .Range("C1:E" & 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:=">0"
        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("A:C").Copy
    'paste from row 3
    ws2.Range("A3").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("2:2").AutoFilter
    End If
End Sub

Re: 1 - Is it because it's assuming Row 2 is the header row of your filter? — CLR

JvdV JvdV · Accepted Answer · 2019-08-23T10:12:25

Try this quick fix, assuming your headers on both sheets are in the first row:

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("SA")
Set ws2 = ThisWorkbook.Worksheets("JC_input")

With ws1
    'assumung that your data stored in column A:D, Sheet1
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A1:D" & lastrow)
    'clear all filters
    .AutoFilterMode = False
    With rng
        'apply filter
        .AutoFilter Field:=4, Criteria1:=">0"
        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("A:A,C:D").Copy
    ws2.Range("A1").PasteSpecial Paste:=xlValues
    'clear all filters
    .AutoFilterMode = False
End With
Application.CutCopyMode = False

End Sub

To answer your questions:

Why this function always copy row A2? Even if value is "0".

That's because you have set a range starting from the second row and applied a filter to it.

We can change that in the code through setting the range from A1:D & Lastrow and also paste it to ws2.Range("A1").

How to copy just value, no formatting?

Yes it's possible, but you'll need to copy and paste as xlValues as explained by @Peh here

The change in code therefore is to .Copy a range and in the next line .PasteSpecial the xlValues.

Is it possible to skip column B when copy? "C" from Sheet1 will be "B" in Sheet2, etc.

Yes instead of copying the whole range, we can specify which columns you would want to copy, this can be a non-contiguous range of columns.

We can change the .Copy part to include only these specific columns we need.

I'm sure the whole thing can be written neater but this should at least do what you are after.

Excel VBA button. Copy rows from Sheet1 to Sheet2 / Condition: column value

1 Answers