Excel VBA: If statement to copy/paste into a new worksheet then delete rows of what was copied

excel vba copy rows to another worksheet based on criteria
excel vba copy data from one sheet to another based on cell value
excel vba cut and paste row to another sheet based on cell value
excel vba copy range to another sheet
vba to copy and paste rows if condition is met
excel vba copy cell value and paste to another cell
excel vba copy rows to another worksheet based on multiple criteria
excel vba copy data from one open workbook to another

Just started learning VBA today to try to make life a bit easier at my new job. I'm essentially trying to look for every instance where column E has the letter "a" copy and paste it into a newly created worksheet called "Aton" then delete the original rows with the "a"s.

I tried to modify the solution found here: VBA: Copy and paste entire row based on if then statement / loop and push to 3 new sheets

When I changed the above solution to make this line "If wsSrc.Cells(i, "E").Value = "a" Then" that's when I run into problems.

    Sub Macro3()
        'Need "Dim"
        'Recommend "Long" rather than "Integer" for referring to rows and columns
        'i As Integer
        Dim i As Long
        'Declare "Number"
        Dim Number As Long
        'Declare a variable to refer to the sheet you are going to copy from
        Dim wsSrc As Worksheet
        Set wsSrc = ActiveSheet
        'Declare a variable to refer to the sheet you are going to copy to
        Dim wsDest As Worksheet
        'Declare three other worksheet variables for the three potential destinations
        Dim wsEqualA As Worksheet
        'Create the three sheets - do this once rather than in the loop
        Set wsEqualA = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        'Assign the worksheet names
        wsEqualA.Name = "Aton"

        'Determine last row in source sheet
        Number = wsSrc.Cells(wsSrc.Rows.Count, "C").End(xlUp).Row

        For i = 1 To Number

        'Determine which destination sheet to use
        If wsSrc.Cells(i, "E").Value = "a" Then
            Set wsDest = wsEqualA

        End If

        'Copy the current row from the source sheet to the next available row on the
        'destination sheet
        With wsDest

            wsSrc.Rows(i).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With

        'Delete row if column E has an a
        If wsSrc.Cells(i, "E").Value = "a" Then

        End If

    Next i
End Sub

Excel VBA: If statement to copy/paste into a new - iOS, Excel VBA: If statement to copy/paste into a new worksheet then delete rows you're copying and (trying to) deleting rows outside the 'If wsSrc. This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data to new workbook. You may also want to read: Excel VBA copy each worksheet to new workbook Access VBA auto generate mass report by group to Excel.

You need to qualify which sheet the original values are on. Change Sheet on the line Set ws = ThisWorkbook.Sheets("Sheet1") to your sheet name.

  1. Create new sheet and set objects
  2. Create range to loop through, LoopRange (E2 down to last row in column)
  3. Loop through LoopRange. If criteria is met, add the cell, MyCell, to a collection of cells (TargetRange)
  4. If the TargetRange is not empty (meaning your criteria was met at least once) then copy header from ws to ns
  5. Copy TargetRange from ws to ns
  6. Delete TargetRange from ws

The benifit if using Union to collect cells is that you avoid many iterations of copy/paste/delete. If you have 50 cells in your range that meet your criteria, you will have 50 instance each for copy/paste/delete for a grand total of 150 actions.

Using the Union method, you will just have 1 instance for each action for a grand total of 3 actions which will boost run time.

Option Explicit

Sub Learning()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim ns As Worksheet: Set ns = Worksheets.Add(After:=(ThisWorkbook.Sheets.Count)) 'ns = new sheet
ns.Name = "Aton"

Dim LoopRange As Range, MyCell As Range, TargetRange As Range

Set LoopRange = ws.Range("E2:E" & ws.Range("E" & ws.Rows.Count).End(xlUp).Row)

For Each MyCell In LoopRange 'Loop through column E
    If MyCell = "a" Then
        If TargetRange Is Nothing Then 'If no range has been set yet
            Set TargetRange = MyCell
        Else 'If a range has already been set
            Set TargetRange = Union(TargetRange, MyCell)
        End If
    End If
Next MyCell

Application.ScreenUpdating = False
    If Not TargetRange Is Nothing Then 'Make sure you don't try to copy a empty range
        ws.Range("A1").EntireRow.Copy ns.Range("A1") 'copy header from original sheet
        TargetRange.EntireRow.Copy ns.Range("A2")
        MsgBox "No cells were found in Column E with value of 'a'"
    End If
Application.ScreenUpdating = True

End Sub

Excel VBA Copy - The Complete Guide to Copying Data, Excel VBA: If statement to copy/paste into a new worksheet then delete rows of what was copied - excel. Follow the below steps to use Excel VBA Copy Paste: Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor. Step 2: Once the VB Editor opens up, click on insert and then click on Insert module to insert a code window. Step 3: Declare a sub-function to start writing the code.

First, don't use ActiveSheet, it can cause multiple problems. If sheet1 is not your source worksheet then change it to meet your needs. I prefer using a filter, as urdearboy suggested, which dosn't require a loop and is faster. I always try to keep the code simple, so try this...

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Aton"

    With Sheet1.UsedRange
        .AutoFilter Field:=5, Criteria1:="a", Operator:=xlFilterValues
        .Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("Aton").Range("A1")
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

VBA to Copy and Paste Rows if Condition is Met, If you just want to copy data from one place to another then I cover that too. 8.3.​3 The worksheet is in a different workbook – reading and writing 10.2.1 How to Copy Rows; 10.2.2 Copying Individual Fields Copy to paste the values only most common way of filtering data in VBA is using a For Loop and If Statement. Rows & Columns – Paste vs. Insert. When pasting rows and columns you have two options: You can paste over the existing row (or column) or you can insert a new row (or column). Let’s look at the difference… Copy & Paste Over Existing Row / Column. This will copy row 1 and paste it into the existing row 5:

VBA Macro to Delete Rows Based on Cell Values, VBA Code to Copy and Paste specific rows from one sheet to another sheet when condition Duration: 10:44 Posted: May 13, 2017 Example #2 – Copy to another Worksheet in the Same Workbook. Now if we want to copy-paste the value from the different worksheets using VBA macro then in the Destination argument we need to reference the sheet name by using WORKSHEETS object then mention the range of cells in that WORKSHEET.

Copying Rows between Worksheets Based on a , In this video I explain how to use VBA to delete rows based on cell values or conditions Duration: 9:32 Posted: Jan 21, 2019 The following two examples of VBA code show you how you can automate copy and pasting values only with an Excel range. This is the VBA code version of how you would manually paste values only in Excel with your mouse and keyboard. The code is simply copying a range and pasting the values only in a destination range.

Excel VBA Copy Paste: The Complete Tutorial And 8 Examples, If you only need to do this type of task sporadically, then you can simply shows rows that have "yes" in column E. Copy the visible rows and paste Worksheets(​"Sheet2") J = 1 ' Start copying to row 1 in target sheet For Each c In Source. I am very new to excel and not quite sure on how to create any of  Copy and paste loop in vba based on cell values. Vba to copy and paste rows if condition is met - Best answers. Excel vba copy cell if criteria met - Best answers. How to create multiple sheets based on cell values - How-To - Excel. Excel vba cut and paste row to another sheet based on cell value - Forum - Excel.