CODEWITHSUNDEEP
excelvba
t l n
t l n

Reputation: 25

VBA - How to copy and data from a worksheet in a certain condition to the last worksheet

I'm new with VBA and I am trying to create a macro for work to make everyone's life easier. My goal is to copy rows (or just copy the data in the first column when the second column is "0") from one worksheet named "Bulk Update" with the condition of column B having the value "0" to the last worksheet, at the bottom of the worksheet after the data. I don't know how to reference the last worksheet name. Here is the code that I made (please don't judge me as I am still new and googling around), which I know is completely wrong... 

Public Sub CNPPrevOOS()
    Worksheets("Bulk Update").Select

    a = Worksheets("Bulk Update").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a

        If Worksheets("Bulk Update").Cells(i, 2).Value = "0" Then


        Selection.Copy

        ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("A1").Value = 100

        Range("A30000").Select

        Selection.End(xlUp).Select

        ActiveCell.Offset(1, 0).Select

        ActiveSheet.Paste

        End If
    Next

End Sub

Upvotes: 0

Views: 36

Answers (1)

SRA
SRA

Reputation: 219

You could try the below code. The data is being filtered for Column 2 = 0. Only those rows are copied and pasted in the last worksheet

Public Sub CNPPrevOOS()

Worksheets("Bulk Update").Select

a = Worksheets("Bulk Update").Cells(Rows.Count, 1).End(xlUp).Row

'Filters the data where column 2 = 0 
ActiveSheet.Range(Cells(1, 1), Cells(a, 2)).AutoFilter Field:=2, Criteria1:="0", Operator:=xlFilterValues

'Select only the filtered cells and copy
Range(Cells(2, 1), Cells(a, 1)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy

 ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Select
 ActiveSheet.Paste Destination:=Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)


        End Sub

Upvotes: 1

Related Questions