'Copy data into new sheet (tab)

      Sheet1                            Sheet2 (output)
      A    B   C                        A    B   C
1   Name1 100  May                1   Name1 100 May        
2   Name2 200  June               2   Name2 200 June
3   Name3      Oct                3   Name3     Oct
4   Name4 300                     4   Name4 300
5   Name5

I want to read the values in Columns B and C. IF any value exist out of 2 then pull that row into new sheet or tab of the same Excel workbook. If columns B and C are blank then skip that row and move onto next row.

excel vba

Solution 1:^[1]

You can use .CurrentRegion() on active range to obtain range which correspond to whole table, and .Copy() method :

Sub CopyData()

Worksheets("Sheet1").Range("A1").Activate

ActiveCell.CurrentRegion.AutoFilter Field:=2, Criteria1:="<>"

ActiveCell.CurrentRegion.AutoFilter Field:=3, Criteria1:="<>"

ActiveCell.CurrentRegion.Copy Worksheets("Sheet2").Range("A1")

End Sub

Slightly more proper, but less readable version is :

Sub CopyDataxxx()

    Dim TargetRange As Range

    Worksheets("Sheet1").Activate
    Range("A1").Activate

    Set TargetRange = ActiveCell.CurrentRegion

    With TargetRange
        .AutoFilter Field:=3, Criteria1:="<>"
        .Copy Worksheets("Sheet2").Range("A1")
        .AutoFilter 'switches off AutoFilter
    End With

End Sub

Solution 2:^[2]

Below is the code with explanations in the comments

Tested

Sub checkNcopy()
'Checking the last populated row
lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

counter = 2

'Copying the variable Names
for i=1 to 3
Worksheets("Sheet2").Cells(1, i) = Worksheets("Sheet1").Cells(1, i)
Next 

For i = 2 To lastRow

'Checking if one of the two columns B and C are populated or not
If Not IsEmpty(Worksheets("Sheet1").Cells(i, 2)) Or Not IsEmpty(Worksheets("Sheet1").Cells(i, 3)) Then

'If one of the two variables are populated then copying the data from sheet1 to sheet2
for j=1 to 3
 Worksheets("Sheet2").Cells(counter, j) = Worksheets("Sheet1").Cells(i, j)
Next
counter = counter + 1
End If

Next

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2

'Copy data into new sheet (tab)

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]