Excel VBA: If statement to copy/paste into a new worksheet then delete rows of what was copied
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 Else 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 Selection.EntireRow.Delete Else 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.
- Create new sheet and set objects
- Create range to loop through,
LoopRange(E2 down to last row in column)
- Loop through
LoopRange. If criteria is met, add the cell,
MyCell, to a collection of cells (
- If the
TargetRangeis not empty (meaning your criteria was met at least once) then copy header from
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.
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") TargetRange.EntireRow.Delete Else 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 .AutoFilter 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.