Macro Mondays - Copy Excel Rows From One Worksheet To Another Worksheet Based On Criteria Using VBA

Hello Excellers, time for some more #MacroMonday fun. Today’s Macro will allow you to quickly copy rows of data from one worksheet to another. This can be really useful if you carry out the same process day after day or week after week in the same workbooks, why not generate a little bit of code and get Excel to do the hard work.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays Series or DOWNLOAD example worksheets you can do so by clicking on the links below.

How To Excel At Excel – Macro Mondays Blog Posts.

[wpdm_package id=’10338′]

So, if you find yourself copying rows 1 to 100 every week that contain the string ‘Invoice’ from one worksheet to another worksheet, then here is a Macro to speed it up. (You can easily amend the VBA code to accommodate the number of rows you copy and the names of the workbook, to make this example work for you). This example is based on my data set below.

Ok let’s get coding………

What Does The Macro Do?

This Macro will identify all of the rows that contain the text string your specify, then delete them. So it will search Column 2 or B in our worksheet named Transaction Type column look for the string ‘Invoice’ if found it will copy the entire row of data from the worksheet named Transactions to our worksheet named Invoices.

How Does It Work?.

This method uses the Auto Filter method which is the most efficient way to identify which rows of data to copy from the Transaction worksheet to the Invoices worksheet especially if you have a large data set to work with.

FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Menu – Module

Step 1. With these two pieces of code we prevent the screen updating and any prompts appearing for the user ( error alerts for exmaple.) This can help speed up code in certain circumstances.

Step 2. We need to declare a variable. This ensures that Excel creates a memory container for our range of cells represented my Rng

Step 3. Our range of cells is set starting at B1 and down to the last row of data.

Step 4. The Filter is applied to the data range and the data is filtered on any row in Column B (2) that contains “Invoices”.

Step 5. Any rows that contain the string “Invoices” are now copied to to the worksheet named “Invoices” using .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(“Invoices”).Range(“A1”) pasting on the new sheet begins with cell A1.

Step 6. The Auto Filter is removed again at this stage, and the application events is reversed to turn them on again and the macro ends.

Do You Want To Copy The Code?

Sub MoveInvoices()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Dim Rng As Range

Set Rng = Range([B1], Range(“B” & Rows.Count).End(xlUp))

On Error Resume Next
    With Rng
        .AutoFilter , field:=1, Criteria1:=”Invoice”
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(“Invoices”).Range(“A1”)
        .AutoFilter
    End With

Application.EnableEvents = True

End Sub