Excel VBA Copy – The Complete Guide to Copying Data

This article covers everything you need to know about Excel VBA Copy methods.

By “Excel VBA Copy Methods”, I mean the different methods you can use to copy data from one range or worksheet to another.

In this article, I’m going to show you the fastest and most efficient way to copy and filter data no matter what your task is.

I say “Copy and Filter” because in most cases you will want to filter the data before you copy it. If you just want to copy data from one place to another then I cover that too.

The article is broken down as follows:

  1. Which Excel VBA Copy and Filter method is the fastest(section 3) – What the professionals know and how you can use it to your advantage.
  2. How to pick the best Excel VBA copy method for each task(section 4) – miss this and you’re wasting countless hours of your time.
  3. How to ensure your application runs at its optimal speed(section 5) – It’s not hard to do once you know the secret.
  4. The Copy and Filter methods explained with full code examples(sections 6 and 7) – Learn these methods and watch your macros run like magic.
    1.  
       

      Contents

      Useful Links

      The following are some related articles with more information about certain topics:

      Ranges and Cells
      Arrays
      Dictionary
      For Loop
      For Each Loop
      The Workbook object
      The Worksheet object

       
       

      Glossary

      Range.Value2 – returns the underlying value in the call. Value is similar but slower and may not return the correct value if there is currency or data formatting. See this article for more information.

      ADO – stands for ActiveX Database Objects. It is a library that allows us to run queries on databases. We can also use it will Excel spreadsheets.

       
       

      Download the Source Code and Data

      In this article I will be using the following dataset:

      Excel VBA Copy Dataset
       
       

      Unless specified, most examples will use the 2 worksheets:

      • Transactions – contains the above data.
      • Report – the data will be copied to this worksheet.

       
       

      CLICK HERE TO GET THE CODE AND DATA FOR THIS POST  

       
       

      How to use this article

      In the next section you can see how the different VBA Copy Methods compare in terms of speed. This is vital when you are determining which one to use.

      You can use section 4 to help you determine which method you should use based on your requirements.

      Section 5 shows you the basic ground work you should have in your application.

      The rest of the article provides a description and code example of each method.

       
       

      Which Excel VBA Copy Method is the Fastest?

      In this section you will see how the different methods compare in terms of speed.

      Using the fastest method is vital when dealing with large amounts of data. As you can see from the results, some of the methods are incredibly slow.

      I have run speed tests on the different methods to see which methods are the fastest for different types of tasks.

      I ran the tests on 20000 , 100000 and 200000 records. You can see the average results for each method in milliseconds in the tables below:

       
       

      Copying Data By Rows

      Average time taken by each method in milliseconds


       
       
      In this test, I filtered by rows that contain a given first name in the first column. About 1% of the records will match the criteria and will be copied. So for 20000 records, there will be 200 records copied.

      You can see that using Autofilter and Advanced filter are the fastest methods for copying and filtering rows.

      At the other end you can see that a For Loop with Range.Copy takes almost 215 times longer than using AutoFilter. I didn’t even run the 200,000 test for this as I expected it would take 15 minutes plus!

       
       

      Copying Data By Individual Fields

      Average time taken by each method in milliseconds

       
       
      Using the Advanced Filter is the clear winner here. Autofilter doesn’t have it’s own copy method so after filtering we have to copy the data using some other method.

      Advanced Filter automatically copies the data and we can specify the columns we want to be copied. Advanced filter then takes care of the rest.

      I have created two different methods of copying using AutoFilter for individual rows. The methods may be faster or slower depending on the number of columns:

      • The Copy Columns method would be slower with more columns to copy.
      • The Delete Columns method would be slower with more columns to delete.

       
       

      Copying Data and Summing Totals

      Excel VBA Su

       
       
      In these tests we are summing data for a particular item. Summing means to get the total amount of a given column for a particular item. For example, getting the total volume for “Laptop Model A”.

      There are 3 VBA copy methods that we can use to do this. These are:

      1. Pivot Tables
      2. For Loop with a Dictionary
      3. ADO(ActiveX Database Objects)

       
       
      Using a Pivot Table is the fastest method for summing data. There is not much difference between the other two methods.

      The downside of the Pivot Table is that the code may be slightly complex for a new user. ADO is very flexible but it does require knowledge of SQL(Structured Query Language).

       
       

      Copying and Transposing Data

      Excel VBA Copy
       
       
      Both methods here are pretty fast. If you are doing a large volume of transpose copies then Application.Transpose tends to be quicker than PasteSpecial Transpose.

       
       

      Which Excel VBA Copy Method Should I Use?

      With so many different methods, you may be feeling overwhelmed. Don’t worry, in this section I will provide a complete guide to selecting the correct Excel VBA copy method to use.

       
       

      CLICK HERE TO GET THE CODE AND DATA FOR THIS POST  

       
       

      Straight Copy with no Filter

      To copy without any filter use the copy by assignment method like this:

      shWrite.Range("F1:G4").Value2 = shRead.Range("A1:B4").Value2
      

       
       
      Excel VBA Copy

       
       

      Filter columns(AND Logic) and Copy Rows

      Advanced Filter is the fastest and easiest method to use if you want to filter by column values using AND logic:

      e.g Item is “Laptop Model A” And Volume is greater than 20

      Excel VBA Copy

       
       

      Filter columns(OR Logic) and Copy Rows

      Advanced Filter is the fastest method to do an OR filter. It’s not possible to do this with AutoFilter.

      e.g Item is “Laptop Model A” Or Volume > 20″

      Excel VBA Copy

       
       

      Filter and Copy Individual Columns

      Sometimes you will not want to copy the entire row. Instead you may want to copy individual columns. Advanced Filter is fastest VBA Copy method for doing this.

      e.g. return the columns Item, Volume and Sales where Item is “Laptop Model A” And Volume > 20″
       

      Excel VBA Copy
       
       

      Sum totals for individual items

      If you want to get the total amount for each item then using a Pivot Table is faster than the other two methods. ADO(ActiveX Database Objects) is slightly faster than using the For Loop with newthe Dictionary.


       
       

      Using a Pivot Table is very flexible. Once you create the table it pretty easy to display the data in many different ways. The downside is that the code may be a bit complex for a VBA beginner.

      ADO is much more flexible but requires some knowledge of SQL(a database query language). It also requires using an external library which may be a bit advanced for a VBA beginner.

      Using the For Loop and a Dictionary requires more code and is less flexible. But it is still pretty fast for up to 200,000 records and doesn’t require any SQL or external libraries.

       
       

      Transpose

      Transposing means to copy data so that the rows become columns and the columns become rows

      Excel VBA Copy
       
       
      There are two methods of transposing data:

       
       
      There is not much difference between these in terms of speed. However, if your application is doing multiple transpose operations then Range.Copy tends to be much slower.
       
       

      Before You Start Copying and Filtering

      No matter which Excel VBA Copy method we use there are some tasks we must perform first.

      These include getting the range of data, turning off certain Excel functionality etc.

      We will look at these tasks in this section.

      The first thing we will look at is the mistake that most VBA beginners make – using Select.

       
       

      Never Use Select

      When copying data in Excel VBA, don’t use Select – ever!

      A big mistake that new VBA users make is thinking that they need to select the cell or range before they copy it.

      For example

      shRead.Activate
      shRead.Range("A1").Select
      shWrite.Activate
      Selection.Copy Activate.Range("H1")
      

       
       
      Keep these two important things in mind before you use VBA to copy data:

      1. You don’t need to select the cell or range of cells.
      2. You don’t need to select or activate the worksheet.

       
       

      You will see Select used in many places online. But you don’t need to use it for copying cells – ever!

       
       

      Speed Up Your Code

      If you want your code to run fast then it is important to turn off certain VBA functionality at the start of our code. We then turn it back on, at the end of our code.

       
       
      We can use the following subs to do this:

      ' Procedure : TurnOffFunctionality
      ' Source    : www.ExcelMacroMastery.com
      ' Author    : Paul Kelly
      ' Purpose   : Turn off automatic calculations, events and screen updating
      Public Sub TurnOffFunctionality()
          Application.Calculation = xlCalculationManual
          Application.DisplayStatusBar = False
          Application.EnableEvents = False
          Application.ScreenUpdating = False
      End Sub
      
      ' Procedure : TurnOnFunctionality
      ' Source    : www.ExcelMacroMastery.com
      ' Author    : Paul Kelly
      ' Purpose   : turn on automatic calculations, events and screen updating
      Public Sub TurnOnFunctionality()
          Application.Calculation = xlCalculationAutomatic
          Application.DisplayStatusBar = True
          Application.EnableEvents = True
          Application.ScreenUpdating = True
      End Sub
      

       
       

      We can use them like this

      Sub Main()
      
          ' Turn off at the start
          TurnOffFunctionality
          
          ' Your code here
          
          ' Turn back on at the end
          TurnOnFunctionality
      
      End Sub
      

       
       

      Sometimes when you run your code, it won’t reach the TurnOnFunctionality code.

      This could because of an error or because you stop the code at a certain point and don’t restart.

      If this happens you can turn everything on again by clicking in the TurnOnFunctionality sub and pressing F5.

       
       

      Get the correct worksheet

      When copying data, we need to specify the range which we will copy from. When using VBA we need to select the worksheet before we can select the range. There are many ways of selecting the worksheet which can be confusing.

      I have broken it down into three scenarios:

      1. The worksheet is in the same workbook as the code.
      2. The worksheet is in a different workbook but we only want to read from it.
      3. The worksheet is in a different workbook but we want to write to it.

       

      You can see the code for each of these scenarios in the next subsections:

       
       

      The worksheet is in the current workbook

      The worksheet is in the current workbook so we can use either:

      1. The code name of the worksheet.
      2. The worksheet name: ThisWorkbook.Worksheets(“worksheet name”).

       
       
      VBA Code name
       
       
      In the screenshot below we have changed the codename and worksheet name:
      Worksheet Code name
       
       
      We would use the following code to reference the sheet

      Sub WorksheetCodeName()
      
          Dim shRead As Worksheet
          
          ' Use the code name
          Set shRead = CodeName
          
          ' OR
          
          ' Use the worksheet name
          Set shRead = ThisWorkbook.Worksheets("SheetName")
          
      End Sub
      

       
       

      The worksheet is in a different workbook – reading only

      If the worksheet is in a different workbook than the code, then we need to open the workbook. In this case we will only be reading from the worksheet so we can simply make it read-only.
       
       
      Then it doesn’t matter if it’s already open. Making it read-only also prevents us from accidentally changing the data in the workbook.

      ' WorksheetRead()
      ' https://excelmacromastery.com/
      '
      ' HOW TO RUN:
      ' 1. Place this code in a new workbook and save it as a .xlsm file.
      ' 2. Create a workbook called Writedata.xlsx and place it in the same
      '    folder as the workbook you just created.
      ' 3. Place any value in cell A1 on sheet1 from Writedata.xlsx.
      ' 4. Run the code(Press F5).
      '
      ' RESULT: The value in cell A1 on sheet1 from Writedata.xlsx
      '         will be displayed in a message box.
      Sub WorksheetRead()
      
          ' Get the full filename and path of "WriteData.xlsx"
          Dim sFilename As String
          sFilename = _
               ThisWorkbook.Path & Application.PathSeparator & "WriteData.xlsx"
      
          ' Open workbook as read-only and store in variable wk
          Dim wk As Workbook
          Set wk = Workbooks.Open(sFilename, ReadOnly:=True)
      
          ' Store the worksheet Sheet1 in the variable shRead
          Dim shRead As Worksheet
          Set shRead = wk.Worksheets("Sheet1")
          
          ' Print the value from the worksheet cell A1
          If IsEmpty(shRead.Range("A1").Value2) Then
              MsgBox "No value was found in cell Sheet1:A1 of WriteData.xlsx."
          Else
              MsgBox "The value found in Sheet1:A1 is of WriteData.xlsx is: " _
                  & shRead.Range("A1").Value2
          End If
          
          wk.Close SaveChanges:=False
      End Sub
      

       
       

      The worksheet is in a different workbook – reading and writing

      Just like the last example, the worksheet is in a different workbook than the code. In this case we want to write to the worksheet in the workbook.

       
       
      We first check if the workbook is already open, and if so we display a message informing the user to close it before running the code again:

      ' WorksheetWrite()
      ' https://excelmacromastery.com/
      '
      ' HOW TO RUN:
      ' 1. Place this code in a new workbook and save it as a .xlsm file.
      ' 2. Create a workbook called Writedata.xlsx and place it in the same
      '    folder as the workbook you just created.
      ' 3. Click in the WorksheetWrite sub and press F5 to run the code.
      ' 4. Open the Writedata.xlsx and check if the value was correctly
      '    written to cell A1 in sheet1.
      '
      ' RESULT: If the file is already open then and error message will be
      '         displayed to the user asking them to close the file.
      '         If the file is not already open then the value 99 will be
      '         written to cell A1 on sheet1 of Writedata.xlsx.
      Sub WorksheetWrite()
      
          Dim sFilename As String
          sFilename = _
               ThisWorkbook.Path & Application.PathSeparator & "WriteData.xlsx"
      
          ' Ensure that the workbook is not already open
          If IsWorkBookOpen("WriteData.xlsx") Then
              MsgBox "Please close workbook " & sFilename & " and try again."
              Exit Sub
          End If
      
          ' Open workbook and store in variable wk
          Dim wk As Workbook
          Set wk = Workbooks.Open(sFilename, ReadOnly:=False)
      
          ' Store the worksheet Sheet1 in the variable shRead
          Dim shRead As Worksheet
          Set shRead = wk.Worksheets("Sheet1")
          
          ' Write a value to the worksheet cell A1
          shRead.Range("A1").Value2 = Now
      
          ' Close the file when finished with it
          wk.Close saveChanges:=True
          
      End Sub
      
      ' Helper function to check if workbook is already open
      Function IsWorkBookOpen(strBookName As String) As Boolean
          
          Dim oBk As Workbook
          
          On Error Resume Next
          Set oBk = Workbooks(strBookName)
          On Error GoTo 0
          
          If Not oBk Is Nothing Then
              IsWorkBookOpen = True
          End If
          
      End Function
      

       
       
      Now that we can get any worksheet we require, we can focus on how to copy the data.

       
       

      Get the Data Range

      In many online Excel VBA Copy examples, you will see a fixed range like Range(“A1:D6”).

      When dealing with real-world applications it is rare that the data will be of a fixed size.

      For example, imagine you stored student marks for each class in a worksheet. Obviously, each class will have a different number of students. Each time you read a worksheet you cannot be sure in advance what the range will be.

      Your code should be able to figure out the full range of data, even if the size changes each time it runs.

      There are two types of data:

      1. Rectangular data where the data is in a list of rows with no blank rows or columns.
      2. Fragmented data where the data may contain blank rows or columns.

       
       

      Using CurrentRegion

      The CurrentRegion property of Range is very useful for returning the entire range of data. CurrentRegion returns all the adjacent data from a given range.

      You can see the CurrentRegion in Excel by selecting a cell and pressing Ctrl + Shift + *.

      In the screenshot below you can see two groups of adjacent data. By selecting any cell with data and pressing Ctrl + Shift + *, Excel will select all the data in that group. This is the current region.

       
       
      VBA CurrentRegion

       
       
      We can also use the CurrentRegion in VBA:

       
       

      The Current region will work as long as there are no blank rows or columns between the data.

      ' CurrentRegion()
      ' https://excelmacromastery.com/
      '
      ' HOW TO RUN:
      ' 1. Create a worksheet called "CurrentRegion".
      ' 2. Add data to all cells in the range B3:D14.
      ' 3. Run this sub(F5).
      '
      ' RESULT: The current region for B3, C9 and D14 will
      '         be displayed in the Immediate Window(Ctrl + G).
      Sub CurrentRegion()
      
           ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("CurrentRegion")
          
          Dim rg1 As Range, rg2 As Range, rg3 As Range
          
          ' Get the current region
          ' All the below examples with return B3:D14
          Set rg1 = shRead.Range("B3").CurrentRegion
          Set rg2 = shRead.Range("C9").CurrentRegion
          Set rg3 = shRead.Range("D14").CurrentRegion
          
          ' Display the addresses to the Immediate Window(Ctrl + G)
          Debug.Print "B3 CurrentRegion is: " & rg1.Address
          Debug.Print "C9 CurrentRegion is: " & rg2.Address
          Debug.Print "D14 CurrentRegion is: " & rg3.Address
      
      End Sub
      

       
       

      Using End with xlUp and xlToLeft

      If there are blank rows and/or blank columns then CurrentRegion will not return all the data.

      For example, in the data shown here there are blank rows and a blank column:
       
       
      Excel VBA Copy
       
       

      We need to get the last row with data and/or the last column with data. We can then use these cells to build our Range.

      ' UseLastRowCol()
      ' https://excelmacromastery.com/
      '
      ' HOW TO RUN:
      ' 1. Create a worksheet called "Fragmented".
      ' 2. Add any data to the cells A1:E13 but make sure you
      '    have the same blank cells as in the above screenshot.
      ' 3. Run this sub(F5)
      '
      ' RESULT: The range A1:E13 will be displayed in the
      '         Immediate Window(Ctrl + G).
      Sub UseLastRowCol()
      
           ' Get the worksheet
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Fragmented")
          
          Dim lastRow As Long, lastCol As Long
              
          ' Get the last cell with data in column A
          lastRow = shRead.Cells(shRead.Rows.count, 1).End(xlUp).row
          
          ' Get the last cell with data in row 1
          lastCol = shRead.Cells(1, shRead.Columns.count).End(xlToLeft).column
          
          Dim rg As Range
          With shRead
              ' Get the full range of data from A1 to last row and column
              Set rg = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
          End With
          
          ' Print the rg address(i.e. A1:E13) to the Immediate Window(Ctrl + G)
          Debug.Print "The range of data is: " & rg.Address
      
      End Sub
      

       
       

      Excel VBA Copy Methods

      In this section we are going to look at the copying methods.

       
       

      CLICK HERE TO GET THE CODE AND DATA FOR THIS POST  

       
       

      There are three ways of copying data in VBA:

      1. The Range.Copy function
      2. The assignment operator(i.e. equals sign)
      3. The Range.CopyFromRecordset function(ADO only)

       
       
      In the following sub sections we will look at these in turn. We will be using these Excel VBA copy methods with the different filter methods.

       
       

      Range.Copy

      We can use the Copy method of the Range to copy data.

      Sub SimpleCopy()
      
           ' Get the worksheet
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          ' Copy the data from A1:D1 to H1:K1
          shRead.Range("A1:D1").Copy Destination:=shRead.Range("H1")
          
          ' Using Destination is optional
          shRead.Range("A1:D1").Copy shRead.Range("H1")
      
      End Sub
      

       
       

      We can copy between worksheets using this Excel VBA method. The only thing that will change in the code is the worksheets that we use.

      ' UsingRangeCopy()
      ' https://excelmacromastery.com/
      '
      ' HOW TO RUN:
      ' 1. Create a workbook called Data.xlsx and place it in the same
      '    folder as the workbook with the code.
      ' 2. Rename sheet1 to "CustomerData".
      ' 3. Add data to the range A1:D19.
      ' 4. Save the file.
      ' 5. Have a worksheet called "Report" in the current workbook
      ' 6. Click in this sub and press F5 to run
      '
      ' RESULT: The data in the range A1:D19 from the CustomerData
      '          worksheet in Data.xlsx will have been copied to
      '         the Report worksheet in the current workbook.
      Sub UsingRangeCopy()
      
          Dim sFilename As String
          sFilename = _
                  ThisWorkbook.Path & Application.PathSeparator & "Data.xlsx"
      
          ' Open a workbook called Data.xlsx from the current folder
          Dim wk As Workbook
          Set wk = Workbooks.Open(sFilename, ReadOnly:=True)
          
          ' shRead stores the worksheet CustomerData
          ' shWrite store the Report sheet
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = wk.Worksheets("CustomerData")
          Set shWrite = ThisWorkbook.Worksheets("Report")
      
          ' Clear the data from the report worksheet
          shWrite.Cells.Clear
      
          ' Copy from CustomerData to Report
          shRead.Range("A1:D19").Copy shWrite.Range("A1")
          
          ' Activate the report worksheet
          shWrite.Activate
          
          ' Close the "Data.xlsx" workbook
          wk.Close
      
      End Sub
      

       
       
      This Range.Copy method copies everything. In other words it will copy values, formulas, formats etc.

      If we only want to copy the data we can use the PasteSpecial property of Range.

       
       

      Using PasteSpecial

      We can use PasteSpecial with Range.Copy to paste the values only

      Sub UsingPasteSpecial()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Clear any existing data from report
          shWrite.Cells.Clear
          
          ' This will copy the values only
          shRead.Range("A1:E13").Copy
          shWrite.Range("A1").PasteSpecial xlPasteValues
          
      End Sub
      

       
       
      You can also copy formats, formulas and comments using PasteSpecial. You can read more about it here.

       
       

      Range.Copy and Transpose

      Transposing data means that the rows become columns and the columns become rows:

      Excel VBA Copy
       
       

      We can use the Range.PasteSpecial function to transpose data:

      Sub RangeCopy_Transpose()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transpose")
          
          ' Clear any existing data
          shRead.Range("F1:I2").Clear
          
          ' This will copy from A1:B4 to F1:I2
          shRead.Range("A1:B4").Copy
          shRead.Range("F1").PasteSpecial Transpose:=True
          
      End Sub
      

       
       

      Copying by Assignment

      We can assign values using the equals sign in VBA.

      We can copy values from one range to another using assignment.

      The value to the right of the equals is placed in cell to the left of the equals as you can see in these examples:

      Sub AssignExamples()"
          
          ' Copy a value to a cell
          Range("A1").Value2 = 56
          Range("A2").Value2 = "Mary"
          
          ' Copy cell to cell
          Range("G1").Value2 = Range("F1").Value2
          Range("G2").Value2 = Range("F2").Value2
          
          ' Copy multiple cells - the source and destination range
          ' must be the same size
          Range("A1:C5").Value2 = Range("D1:F5").Value2
          
      End Sub
      

       
       

      The code below shows an example of copying the data from the data shown in the screenshot at the start of this section.

      We will copy the range A1:D11 on the worksheet Transactions to the range A1:D11 on worksheet Report.

      ' UsingAssignment()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' RESULT: The data will be copied from the Transaction worksheet
      '         to the report worksheet
      Sub UsingAssignment()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Copy the data
          shWrite.Range("A1:D11").Value2 = shRead.Range("A1:D11").Value2
      
      End Sub
      

       
       

      The key thing to remember about assigning values is that the destination must be the same size as the source.

      If it is smaller than the source range then not all the data will be written.

      If it is bigger than the source then there will be #N/A errors in the extra cells.

       
       

      Assignment with Transpose

      Excel VBA Copy
       
       
      We can use the WorksheetFunction.Transpose function to transpose using the assignment operator(i.e. equals sign) to copy:

      Sub TransposeAssign()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transpose")
          
          ' Clear any existing data
          shRead.Range("F1:I2").Clear
          
          shRead.Range("F1:I2").Value2 = _
                  WorksheetFunction.Transpose(shRead.Range("A1:B4").Value2)
      
      End Sub
      

       
       

      Using Range.CopyFromRecordset

      A Recordset is the collection of data we get when we run a database query.

      We can run database queries on worksheet data using ADO (ActiveX Data Objects).

      We will look at this Excel VBA Copy method in more detail in the ADO section below.

       

      Excel VBA Filter Methods

      Whenever you are copying data in VBA you will almost always apply some type of filter. This could be selection rows based on certain criteria or it could be summing data.

      In this section, we are going to look at the various ways of filtering data.

       
       

      Filtering using For and If

      The most common way of filtering data in VBA is using a For Loop and If Statement.

      This is the least flexible and most long-winded method of filtering data in VBA. By long-winded, I mean that it requires lots of code compared to the other methods.

      Using For and IF is the method you will see used the most in examples or in legacy VBA code.

      We use the For Loop to read through data one row at a time. For each row, we will use the If Statement to check for certain criteria.

      Here is a simple outline:

      Sub ForIf_Basic()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim i As Long
          For i = 1 To 13
              If shRead.Range("A" & i).Value2 = "Laptop Model A" Then
                  ' Code for copying here
              End If
          Next i
          
      End Sub
      

       
       

      We can use both And and Or operators in the if statement.
       
       
      Here are some examples:

      If Item = "Laptop Model A" And Volume > 20 Then
      
      If Item = "Laptop Model A" Or Volume > 20 Then
      
      If Item = "Laptop Model A" Or Item = "Laptop Model B" Then
      

       
       

      How to Copy Rows

      The following code reads through the data. It checks each line to see if the first cell contains “Laptop Model A” .

      If the row with these criteria is found, the row will be copied to the output worksheet.

      The first version of the code below will use Range.Copy and the second will use the assign copy method:

      ' For_RangeCopy()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub For_RangeCopy()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          With shWrite
      
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Read through the data
          Dim i As Long, row As Long
          row = 1
          For i = 1 To rg.Rows.count
              
              If rg.Cells(i, 1).Value2 = "Laptop Model A" Or i = 1 Then
                  
                  ' Copy using Range.Copy
                  rg.Rows(i).Copy
                  shWrite.Range("A" & row).PasteSpecial xlPasteValues
                  
                  ' move to the next output row
                  row = row + 1
                  
              End If
              
          Next i
          
      End Sub
      

       
       

      ' For_Assign()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub For_Assign()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          With shWrite
      
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Read through the data
          Dim i As Long, row As Long
          row = 1
          For i = 1 To rg.Rows.count
              
              If rg.Cells(i, 1).Value2 = "Laptop Model A" Or i = 1 Then
                 
                  ' Get the destination range
                  Dim rgDest As Range
                  Set rgDest = _
                      shWrite.Range("A" & row).Resize(1, rg.Columns.count)
                  
                  ' Copy using Assignment
                  rgDest.Value2 = rg.Rows(i).Value2
                  
                  ' move to the next output row
                  row = row + 1
                  
              End If
              
          Next i
          
      End Sub
      

       
       

      In the above code, we use Range.Resize when copying by assignment. You can read more about this here.

       
       

      Using an Array to Read Faster

      If you are reading through a lot of data then reading directly from the worksheet range is very slow.

      It is much quicker to copy the data to an array and then read through that array. Then when we are finished, we write the array back to the worksheet

      We cannot use Range.Copy because it obviously can only be used on an actual range.

      Let’s update our last example so it will use an array:

      Sub ForUsingArray_Assign()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Copy the range to an array
          Dim arr As Variant
          arr = shRead.Range("A1").CurrentRegion.Value2
          
          With shWrite
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Read through the data
          Dim i As Long, j As Long
          Dim row As Long, Columns As Long
          row = 1
          For i = LBound(arr) To UBound(arr)
              
              ' If "Laptop Model A" or header row then copy
              If arr(i, 1) = "Laptop Model A" Or i = 1 Then
                  
                  ' Copy each column
                  For j = LBound(arr, 2) To UBound(arr, 2)
                      shWrite.Cells(row, j).Value2 = arr(i, j)
                  Next j
                  
                  ' move to the next output row
                  row = row + 1
                  
              End If
              
          Next i
          
      End Sub
      

       
       

      We could also use the Worksheet Function Index to copy. However, it is incredibly slow. In the speed tests it was over 4000 times slower than using the AutoFilter.

      ' Copy using Index
      Columns = UBound(arr, 2)
      shRepAssign.Range("A" & row).Resize(1, Columns).Value2 = _
              Application.WorksheetFunction.Index(arr, i, 0)
      

       
       

      Copying Individual Fields

      Copying individual items in a row is actually very straightforward.

      We simply copy each cell individually:

      ' For_Assign_Individual()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub For_Assign_Individual()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          With shWrite
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Read through the data
          Dim i As Long, rgWrite As Range, row As Long
          row = 1
          For i = 1 To rg.Rows.count
          
              ' If "Laptop Model A" or the header row
              If rg.Cells(i, 1).Value2 = "Laptop Model A" Or i = 1 Then
          
                  ' Copy from column A to column A
                  shWrite.Cells(row, 1).Value2 = rg.Cells(i, 1).Value2
                  
                  ' Copy from column E to column B
                  shWrite.Cells(row, 2).Value2 = rg.Cells(i, 5).Value2
                  
                  ' Move to the next row for writing
                  row = row + 1
              
              End If
              
          Next i
      
      End Sub
      

       
       

      The method below using Range.Copy – it is very slow so it is best to avoid using it.

      ' WARNING: DON'T USED THIS METHOD - IT IS INCREDIBLY SLOW
      Sub For_RangeCopy_Individual()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Read through the data
          Dim i As Long, rgWrite As Range, row As Long
          row = 1
          For i = 1 To rg.Rows.count
          
              ' If "Laptop Model A" or the header row
              If rg.Cells(i, 1).Value2 = "Laptop Model A" Or i = 1 Then
          
                  ' Copy using Range.Copy
                  rg.Cells(i, 1).Copy
                  shWrite.Range("A" & row).PasteSpecial xlPasteValues
                  
                  rg.Cells(i, 5).Copy
                  shWrite.Range("B" & row).PasteSpecial xlPasteValues
                  
                  ' Move to the next row for writing
                  row = row + 1
              
              End If
              
          Next i
      
      

       
       
      In this example we use an array with the For loop to copy. It is the fastest way to copy individual columns using the For loop but it is still way slower than using the AutoFilter.

      ' ForUsingArray_Assign_Individual()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub ForUsingArray_Assign_Individual()
      
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Get the range
          Dim arr As Variant
          arr = shRead.Range("A1").CurrentRegion.Value2
          
          ' Read through the data
          Dim i As Long, rgWrite As Range, row As Long
          row = 1
          For i = LBound(arr) To UBound(arr)
          
              ' If "Laptop Model A" or the header row
              If arr(i, 1) = "Laptop Model A" Or i = 1 Then
          
                  ' Copy from column A to column A on the report worksheet
                  shWrite.Cells(row, 1).Value2 = arr(i, 1)
                  
                  ' Copy from column E to column B on the report worksheet
                  shWrite.Cells(row, 2).Value2 = arr(i, 5)
                  
                  ' Move to the next row for writing
                  row = row + 1
              
              End If
              
          Next i
      
      End Sub
      

       
       
      If the original data has a lot of columns and you only require a few of them, then this method may be as fast as AutoFilter but you will need to test both methods.
       
       

      Summing Totals

      In the data shown below we want to get the total sales for each item:
      Excel VBA Copy

       
       
      Our result should look like this:

      Excel VBA Copy

       
       
      Summing data like this is much trickier than simply filtering data.

      For each item, we need to store a running total somewhere. Each time we read an item we need to either create a new entry or add the current sales amount to the existing entry.

      The easiest way to do this is to use a Dictionary.

       
       
      You can see the code to do this below. You will notice that it is split into two parts:

      • In part one we read the values from the worksheet to the dictionary.
      • In part two we write the values from the dictionary to the worksheet.

       
       

      ' ForDictionary_Assign_Sum()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' IMPORTANT: Add the Dictionary reference to use the dictionary
      '            Tools->Reference and check "Microsoft Scripting Runtime"
      Sub ForDictionary_Assign_Sum()
      
          ' PART 1 - Read the data
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Create the dictionary
          Dim dict As New Dictionary
          
          ' Read through the data
          Dim i As Long, Item As String, Sales As Long
          For i = 2 To rg.Rows.count
              ' Store the values in a variable
              Item = rg.Cells(i, 1).Value2
              Sales = rg.Cells(i, 5).Value2
              
              ' The item will be automatically added if it doesn't exist
              dict(Item) = dict(Item) + Sales
          Next i
          
          ' PART 2 - Write the data
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
              ' Write header
              .Cells(1, 1).Value2 = "Item"
              .Cells(1, 2).Value2 = "Sales"
              
          End With
          
          Dim key As Variant, row As Long
          row = 2
          ' Read through each item in the Dictionary
          For Each key In dict.Keys
          
              shWrite.Cells(row, 1) = key
              shWrite.Cells(row, 2) = dict(key)
              
              row = row + 1
          Next key
          
      End Sub
      

       
       

      Summing Multiple Totals

      In the previous example we were summing one item. This time we are going to sum the volume and the price fields.

      The Dictionary stores a key and a value. If we want to store more than one value then we use a class module.

      In the next example, I have split up the code into procedures to make it easier to read and to be more flexible:

      ' Class Module: clsSales
      Public Items As String
      Public Volume As Long
      Public Sales As Long
      

       

      Sub CreateReport()
      
          ' Declare a Dictionary variable
          Dim dict As Dictionary
      
          ' Read the data to the Dictionary
          Set dict = ReadData
          
          ' Write the Data from the Dictionary to the Worksheet
          WriteData dict
      
      End Sub
      
      ' Read the data from the worksheet into a Dictionary
      '
      ' IMPORTANT: Add the Dictionary reference to use the dictionary
      ' Tools->Reference and check "Microsoft Scripting Runtime"
      Private Function ReadData() As Dictionary
          
          ' Create the dictionary
          Dim dict As New Dictionary
          
          ' Get the worksheets
          Dim shRead As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Read through the data
          Dim i As Long, Item As String, Volume As Long, Sales As Long
          Dim oSales As clsSales, oSalesCurrent As clsSales
          For i = 2 To rg.Rows.count
      
              Item = rg.Cells(i, 1).Value2
              Volume = rg.Cells(i, 4).Value2
              Sales = rg.Cells(i, 5).Value2
              
              ' Check if the Item has already been added
              If dict.Exists(Item) = False Then
                  ' Add Item and new class module object
                  Set oSales = New clsSales
                  dict.Add Item, oSales
              End If
              
              ' Get the current item
              Set oSalesCurrent = dict(Item)
              
              ' Update the data
              With oSalesCurrent
                  .Volume = .Volume + Volume
                  .Sales = .Sales + Sales
              End With
              
          Next i
          
          Set ReadData = dict
          
      End Function
      
      ' Write the summed data to the worksheet
      Sub WriteData(dict As Dictionary)
      
          Dim shWrite As Worksheet
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "0"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              
              ' Write header
              .Cells(1, 1).Value2 = "Item"
              .Cells(1, 2).Value2 = "Volume"
              .Cells(1, 3).Value2 = "Sales"
              
          End With
          
          Dim key As Variant, oData As clsSales, row As Long
          row = 2
          
          ' Read through each item in the Dictionary
          For Each key In dict.Keys
          
              Set oData = dict(key)
              
              shWrite.Cells(row, 1) = key
              shWrite.Cells(row, 2) = oData.Volume
              shWrite.Cells(row, 3) = oData.Sales
              
              row = row + 1
              
          Next key
          
      End Sub
      

       
       

      Using AutoFilter

      The methods that we have looked at so far require a lot of code.

      There is actually a much simpler way of reading and filtering data. We can use the AutoFilter. This is the filter we use in Excel to filter data. Once the data is filtered we can easily copy and paste it.

      VBA AutoFilter Excel

      VBA AutoFilter Excel

       
       
      In terms of speed, AutoFilter came out on top in the speed tests that I performed. It is the best one to use for selecting or copying either rows or individual columns.

       
       
      These are some examples of using AutoFilter in VBA:

      ' Field 1 = "Ipsum Corporation" and field 3 >=50
      rg.AutoFilter Field:=1, Criteria1:="Ipsum Corporation" _ 
             , Operator:=xlAnd, Field:=3, Criteria1:=">=50"
      
      ' Field 1 starts with "I"
      rg.AutoFilter Field:=1, Criteria1:="I*"
      
      ' Field 1 ends with "Ltd"
      rg.AutoFilter Field:=1, Criteria1:="*Ltd"
      
      ' Field 1 contains "Su"
      rg.AutoFilter Field:=1, Criteria1:="*Su*"
      
      ' Field 1 does not contain "Su"
      rg.AutoFilter Field:=1, Criteria1:="<>*Su*"
      
      ' Get the top 10 items based on the value in Field 3
      rg.AutoFilter Field:=3, Criteria1:="10", Operator:=xlTop10Items
      
      ' Field 3 is in the value list of 100,17 and 2
      rg.AutoFilter Field:=3, Criteria1:=Array("100", "17", "2") _
           , Operator:=xlFilterValues
      

       
       
      Now that we understand the basics, let’s look at some practical examples of using AutoFilter.
       
       

      How to Copy Rows

      Using AutoFilter is the fastest way to copy filtered rows of data.

      Let’s use AutoFilter to copy all the rows that contain “Laptop Model A”.

      The following code shows how to do this:

      ' AutoFilter_RangeCopy_Row()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub AutoFilter_RangeCopy_Row()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Remove any existing filters
          rg.AutoFilter
          
          ' Apply the Autofilter
          rg.AutoFilter Field:=1, Criteria1:="Laptop Model A"
          
          ' Copy the data using Range Copy
          shRead.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
          shWrite.Range("A1").PasteSpecial xlPasteValues
          
          ' Remove any existing filters
          rg.AutoFilter
          
          ' Active the output sheet so it is visible
          shWrite.Activate
      
      End Sub
      

       
       
      We can also use assign to copy the data. It is almost as fast but requires slightly more complex code.

      It is actually not necessary to use this code but I am including it for completeness:

      ' AutoFilter_Assign_Row()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      Sub AutoFilter_Assign_Row()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
              
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Remove any existing filters
          rg.AutoFilter
          
          ' Apply the Autofilter
          rg.AutoFilter Field:=1, Criteria1:="Laptop Model A"
           
          ' Get the read range
          Dim rgRead As Range
          Set rgRead = _
              shRead.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
      
          Dim rgRow As Range, row As Long
          row = 1
          ' Read through each row
          For Each rgRow In rgRead.Rows
              
              ' Copy the row
              shWrite.Cells(row, 1).Resize(1, rgRead.Columns.count).Value2 _
                         = rgRow.Value2
              
              ' move to the next output row
              row = row + 1
              
          Next rgRow
          
          ' Remove any existing filters
          rg.AutoFilter
      
      End Sub
      
      

       
       

      Using AutoFilter code is much easier to write because we don’t need a For Loop and an If Statement. We can filter the data in just one line.

       

      Copying Individual Fields

      Copy individual cells is more long winded than copying the row.

      It may seem counter intuitive at first but we have to specify the cells we are copying. With a row, we can just copy the entire thing.

      There are 2 main ways we can do this:

      1. Copy the individual columns from the filtered data.
      2. Copy filtered data and delete the columns that are not required.

       
       

      Copy the Columns

      The first method is the most basic way to do this. We simple copy each column that we require

      ' AutoFilter_CopyColumns()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' Details: Use AutoFilter with Range.Copy to filter and copy individual
      '          column data.
      '          The resulting data should contain the columns Items and Sales.
      Sub AutoFilter_CopyColumns()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Remove any existing filters
          rg.AutoFilter
          
          ' Apply the Autofilter
          rg.AutoFilter Field:=1, Criteria1:="Laptop Model A"
          
          Dim arr As Variant
          arr = Array(1, 5)
          
          ' Remove unnecessary columns
          Dim rgOut As Range, i As Long, column As Long
          Set rgOut = shWrite.Range("A1").CurrentRegion
          column = 1
          For i = LBound(arr) To UBound(arr)
              rg.Cells(1, arr(i)).EntireColumn.Copy
              shWrite.Cells(1, column).PasteSpecial xlPasteValues
              column = column + 1
          Next i
          
          ' Remove any existing filters
          rg.AutoFilter
          
      End Sub
      

       
       

      Copy and Delete Unwanted Columns

      The second method is the fastest for data with a reasonable number of columns. We simply copy all the data and delete the columns we don’t need:

      ' AutoFilter_DeleteColumns()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' Details: Use AutoFilter with Range.Copy to filter and copy individual
      '          column data.
      '          Copy the entire data and then delete the unnecessary columns.
      '          The result should be Item and Sales columns only.
      Sub AutoFilter_DeleteColumns()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
          
          ' Remove any existing filters
          rg.AutoFilter
          
          ' Apply the Autofilter
          rg.AutoFilter Field:=1, Criteria1:="Laptop Model A"
      
          ' Copy the filtered data to the output worksheet
          rg.SpecialCells(xlCellTypeVisible).Copy
          shWrite.Range("A1").PasteSpecial xlPasteValues
          
          ' Remove unnecessary columns
          Dim rgOut As Range, i As Long
          Set rgOut = shWrite.Range("A1").CurrentRegion
          For i = rgOut.Columns.count To 1 Step -1
              Select Case i
                  Case 1, 5
                  Case Else
                      rgOut.Columns(i).EntireColumn.Delete
              End Select
          Next i
          
          ' Remove any existing filters
          rg.AutoFilter
          
      End Sub
      

       
       

      Summing Totals

      We cannot use the Filters to sum data. We need to use either a For Loop with a Dictionary or ADO.

       
       

      Using Advanced Filter

      Advanced Filter has 4 major advantages over AutoFilter:

      1. It has more advanced filtering.
      2. It can automatically copy the results to another range.
      3. When copying the results it automatically copies the format.
      4. You can select specific columns and the order of columns.

       
       

      The AutoFilter has a drawback when filtering data. We cannot use the logic:

      If Value1 is in Column1 OR Value2 is in Column2

      For example, we cannot say we want all the records where the Item is “Laptop Model A” or Volume > 6.

      We can use the AdvancedFilter to get around this. AdvancedFilter is very similar to AutoFilter. The major difference is that the criteria must be a range on the worksheet.

       
       
      The screenshot below shows how to use AND. It means item is “Laptop Model A” and Volume > 16. The Criteria Range for this must be G1:K2.
      Excel VBA Copy

       
       
      The result of using these criteria is:

      Excel VBA Copy

       
       
      The screenshot below shows how to use OR. It means item is “Laptop Model A” OR Volume > 16. The Criteria Range for this must be G1:K3.
      Excel VBA Copy

       
       
      The result of using these criteria is

      Excel VBA Copy

       
       
      The code below shows how we create an advanced filter for the above and criteria.

      Range("A1:D12").AdvancedFilter Action:=xlFilterInPlace _
           , CriteriaRange:=Range("G1:K2")
      

       
       
      For OR it would be

      Range("A1:D12").AdvancedFilter Action:=xlFilterInPlace _
           , CriteriaRange:=Range("G1:K3")
      

       

      Important: You will notice here that the Criteria range is different for the two examples. Make sure that you don’t have a blank line in the criteria (e.g. G1:K4 instead of G1:K3). This will result in all records being returned i.e. no filter being applied.

       
       

      How to Copy Rows

      Advanced filter will not only automatically copy the results for us. It is also the fastest VBA method for copying and filter in most scenarios.

      We simple set the Action parameter to xlFilterCopy and set the CopyToRange parameter to the output range:

      ' ----------------------------------------------------------------
      ' Procedure Name: AdvancedFilterExample
      ' Purpose: An example of using the Advanced Filter to copy rows
      '
      ' Result: The result depends on the values set in the criteria range
      '         G to K columns on the "Transaction Filter" worksheet.
      ' Website: https://excelmacromastery.com/
      ' ----------------------------------------------------------------
      Sub AdvancedFilterExample()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions Filter")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Clear any existing data
          shWrite.Cells.Clear
      
          ' Remove the any existing filters
          If shRead.FilterMode = True Then
              shRead.ShowAllData
          End If
          
          ' Get the source data range
          Dim rgData As Range, rgCriteria As Range
          Set rgData = shRead.Range("A1").CurrentRegion
          
          ' IMPORTANT: Do not have any blank rows in the criteria range
          Set rgCriteria = shRead.Range("G1:K2")
         
          ' Apply the filter
          rgData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rgCriteria _
                      , CopyToRange:=shWrite.Range("A1")
      
       End Sub
      

       
       

      Copying Individual Fields

      To copy specific columns is pretty simple. We place the columns in our output range and Advanced Filter will only write data to this columns.

      For example, If we only want the Item and Sales columns then we need to make two changes to Advanced filter example above:

      1. Set the Headers before we run advanced filter.
      2. Set the CopyToRange parameter to include the Range of headers.

       
       
      We can see the updated code here:

      ' ----------------------------------------------------------------
      ' Procedure Name: AdvancedFilter_Columns
      ' Purpose: Uses Advanced Filter to copy individual columns
      '
      ' Result: The result will be the Item and Sales column in the Report
      '         worksheet.
      ' Website: https://excelmacromastery.com/
      ' ----------------------------------------------------------------
      Sub AdvancedFilter_Columns()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions Filter")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Clear any existing data
          shWrite.Cells.Clear
          
          ' Specify the output columns
          shWrite.Range("A1").Value2 = "Item"
          shWrite.Range("B1").Value2 = "Sales"
      
          ' Remove the filter
          If shRead.FilterMode = True Then
              shRead.ShowAllData
          End If
          
          ' Get the source data range
          Dim rgData As Range, rgCriteria As Range
          Set rgData = shRead.Range("A1").CurrentRegion
          
          ' IMPORTANT: Do not have any blank rows in the criteria range
          Set rgCriteria = shRead.Range("G1:K2")
         
          ' Apply the filter
          rgData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rgCriteria _
                      , CopyToRange:=shWrite.Range("A1:B1")
      
      End Sub
      
      

       
       

      Using ADO and SQL

      ADO is an external Library that allows us to perform queries on databases. It stands for ActiveX Database Objects.

      ADO can also perform queries on data in worksheets. This means we can do very powerful queries that run very fast.

      Just like using AutoFilter and Advanced filter, we can use most of the same VBA code each time. The main difference will be the query line. The disadvantage is that it requires some basic knowledge of SQL.

      The following code returns all the records where the item is “Laptop Model A”:

      ' ReadFromWorksheetADO()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' Details: Returns all the records for item "Laptop Model A".
      Sub ReadFromWorksheetADO()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          With shWrite
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' To add ADO reference select Tools->Reference and
          ' check "Microsoft ActiveX Data Objects Objects 6.1 Library"
          Dim conn As New ADODB.Connection
          conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes;"";"
      
          ' Store the query in a string
          Dim query As String
          query = "Select * from [" & shRead.name _
                          & "$] Where Item='Laptop Model A' "
          
          ' Run the query and store in a recordset
          Dim rs As New Recordset
          rs.Open query, conn
      
          ' Write header
          Dim i As Long
          For i = 0 To rs.Fields.count - 1
              shWrite.Cells(1, i + 1).Value2 = rs.Fields(i).name
          Next i
          
          ' Write data
          shWrite.Range("A2").CopyFromRecordset rs
          
          ' Close the connection
          conn.Close
          
      End Sub
      

       
       
      Here are some examples of ADO queries

      ' Item is "Laptop Model A" AND the volume is greater than or equal 20
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item = 'Laptop Model A' and Volume>=20"
      
      ' Item is "Laptop Model A" OR the volume is greater than or equal 20
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item = 'Laptop Model A' or Volume>=20"
      
      ' Item starts with "Laptop"  
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item like 'Laptop%' "   
      
      ' Item Ends with "Model A"
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item like '%Model A' "
      
      ' Item contains "top"
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item like '%top%' "
      
       ' Item does not contain "top"
      query = "Select Item,Day,Price,Volume,Sales from [Transactions$] " _
               & " Where Item Not like '%top%' "
      
      ' Return the total volume for each monitor type
      query = "Select Item, Sum(Volume) As [Total Vol] from [Transactions$] " _
               & " Where Item like 'Monitor%' " _
               & " Group by Item"
      

       
       

      It is important to note that for multiple queries you don’t need to connect to the workbook each time.

      One connection to the workbook is enough. Then you can run all your queries. When your queries are finished you can close the connection.

      The following outline shows what I mean:

      ' Open the connection
      Dim conn As New ADODB.Connection
      conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & ThisWorkbook.FullName & ";" & _
              "Extended Properties=""Excel 12.0;HDR=Yes;"";"
      
      ' Run all queries
      ...
      ...
      
      ' When finished close the connection
      conn.Close
      

       
       

      How to Copy Rows Using ADO

      Copying a row is pretty straightforward using ADO.

      There is a function Range.CopyFromRecordset which writes out the entire data from the query.

      This is very convenient but it doesn’t copy the header and it is slower than many other methods.

       
       
      In the code below we write out all the items that are “Laptop Model A”:

      ' ADO_CopyRow()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' Details: Returns the records for item "Laptop Model A".
      Sub ADO_CopyRow()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions Filter")
          Set shWrite = ThisWorkbook.Worksheets("Report")
           
          With shWrite
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "dd/mm/yyyy"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              .Columns(4).NumberFormat = "0"
              .Columns(5).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' To add ADO reference select Tools->Reference and
          ' check "Microsoft ActiveX Data Objects Objects 6.1 Library"
          Dim conn As New ADODB.Connection
          conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes;"";"
      
          ' Store the query in a string
          Dim query As String
          query = "Select Item,Day,Price,Volume,Sales from [" & shRead.name _
                  & "$] Where Item='Laptop Model A' "
          
          Dim rs As New Recordset
          rs.Open query, conn
          
          ' Write header
          Dim i As Long
          For i = 0 To rs.Fields.count - 1
              shWrite.Cells(1, i + 1).Value2 = rs.Fields(i).name
          Next i
          
          ' Write data
          shWrite.Range("A2").CopyFromRecordset rs
          
          conn.Close
          
      End Sub
      

       
       

      Copying Individual Fields

      Copying individual fields using ADO couldn’t be simpler.

      We specify the fields we want in the Select statement:

      ' Select all the fields
      sQuery = "Select Item,Day,Price,Volume,Sales from [Transactions$]"
      
      ' Select Item and Sales
      sQuery = "Select Item,Sales from [Transactions$]"
      
       ' Select Volume
      sQuery = "Select Volume from [Transactions$]"
      
       ' Select Price, Item - changes order of fields
      sQuery = "Select Item,Price from [Transactions$]"
      
      

       

      Summing Totals

      Summing totals is also simple. It only requires adding a Group By statement to the query

      Let’s get the total volume and sales for each item:

      ' ADO_SumColums()
      ' https://excelmacromastery.com
      '
      ' Requirements:
      ' 1. A worksheet "Transactions" containing data from this
      ' section: "The Dataset for this Article".
      ' 2. A worksheet for output called "Report".
      '
      ' Details: Returns the total sales amount for each item.
      Sub ADO_SumColums()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions Filter")
          Set shWrite = ThisWorkbook.Worksheets("Report")
           
          With shWrite
          
              ' Clear the data in output worksheet
              shWrite.Cells.ClearContents
              
              ' Set the cell formats
              .Columns(2).NumberFormat = "0"
              .Columns(3).NumberFormat = "$#,##0;[Red]$#,##0"
              
          End With
          
          ' To add ADO reference select Tools->Reference and
          ' check "Microsoft ActiveX Data Objects Objects 6.1 Library"
          Dim conn As New ADODB.Connection
          conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes;"";"
      
          ' Store the query in a string
          Dim query As String
          query = "Select Item,Sum(Volume) As [Total Volume] " _
              & ",Sum(Sales) As [Total Sales] " _
              & "from [" & shRead.name & "$] Group By Item "
          
          Dim rs As New Recordset
          rs.Open query, conn
          
          ' Write header
          Dim i As Long
          For i = 0 To rs.Fields.count - 1
              shWrite.Cells(1, i + 1).Value2 = rs.Fields(i).name
          Next i
          
          ' Write data
          shWrite.Range("A2").CopyFromRecordset rs
          
          conn.Close
          
      End Sub
      

       
       

      Pivot Table

      Using a Pivot Table is a very powerful way to sum data. It is faster than using the other summing methods.

      It is more flexible than using For with a Dictionary but easier to use than ADO as no knowledge of SQL is necessary.

      We don’t actually need a copy method with the Pivot Table. We just need to specify range where the Pivot table will be created.

      Summing Data

      The Pivot Table automatically sums values.

      We have rows and value fields in a Pivot Table. We can use these to sum data.

      1. The Row field is the thing that we plan to get the total of e.g. Items.
      2. The Value field is the value that we wish to sum e.g. Volume, Sales.

       
       

      We can set these fields like this:

      ' Set the Row fields
      .PivotFields("Item").Orientation = xlRowField
      
      ' Set the Value fields
      .PivotFields("Volume").Orientation = xlDataField
      .PivotFields("Sales").Orientation = xlDataField
      

       
       
      The full PivotTable code looks like this:

      Sub PivotTable_Sum()
      
          ' Get the worksheets
          Dim shRead As Worksheet, shWrite As Worksheet
          Set shRead = ThisWorkbook.Worksheets("Transactions")
          Set shWrite = ThisWorkbook.Worksheets("Report")
          
          ' Get the range
          Dim rg As Range
          Set rg = shRead.Range("A1").CurrentRegion
         
          ' Clear any existing pivot tables
          Dim piv As PivotTable
          For Each piv In shWrite.PivotTables
              piv.TableRange2.Clear
          Next piv
          
          ' Clear the data in output worksheet
          shWrite.Cells.ClearContents
        
          ' Create the cache
          Dim ptCache As PivotCache
          Set ptCache = ThisWorkbook.PivotCaches.Create( _
              SourceType:=xlDatabase _
              , SourceData:=rg _
              , Version:=xlPivotTableVersion15)
          
          ' Create the table
          Dim ptTable As PivotTable
          Set ptTable = shWrite.PivotTables.Add( _
                   PivotCache:=ptCache _
                  , TableDestination:=shWrite.Range("A1"))
          
          ' Set the name and style
          ptTable.name = "pvItems"
          ptTable.TableStyle2 = "PivotStyleDark14"
          
          ' Set the fields
          Dim ptField As PivotField
          With ptTable
      
              ' Set the rows fields
              .PivotFields("Item").Orientation = xlRowField
              
              ' Set the data(value) fields
              .PivotFields("Volume").Orientation = xlDataField
              .PivotFields("Sales").Orientation = xlDataField
              
          End With
          
      End Sub
      

       
       
      If we want to filter with the PivotTable then it is a bit long winded as we have to set each item individually.

      To add a filter we can use the code below. Add this code before the “End With” line in the above code.

      ' Filter items
      Dim pvItem As PivotItem
      For Each pvItem In .PivotFields("Item").PivotItems
          If pvItem.Caption = "Laptop Model A" Then
              pvItem.Visible = True
          Else
              pvItem.Visible = False
          End If
      Next pvItem
      

       
       

      Conclusion

      That concludes this article on copying and filtering data using the different Excel VBA Copy methods.

      If you think something is missing or you have any questions or queries then please leave a comment below.

       
       

      What’s Next?

      Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

      Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

      (NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

       

      Get the Free eBook

      How To Ace the 21 Most Common Questions in VBA

      Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the website, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

      Free VBA eBook


35 comments

  1. Paul, you have exceeded my expectations This is an exhaustive post – the simple Ctl+C has been expanded well beyond what I expected could be achieved with “Copying” in Excel. This is a first observation, which I expect will be confirmed when I work through the code in the Post – thanks, Paul.
    John
    Mount Waverley Victoria Australia

  2. Thanks Paul. I found the article to be very informative and it clearly spells out the different methods of copying data , how and when to use each method. Well done!

  3. I have test some of the contents and find it very helpfull and developing. It really speed up the worok with copying and I agree to Johns comments of the effect on simple Database coding. Just one commet about finding area in a sheet. I use” Sheet1.UsedRange” that will found the area.

  4. Thank you for another amazing article/novel so full of information that I am going to spend half my day playing around in the VBA Editor.
    I never thought to use the ADO library to query in the existing worksheet and I can’t wait to apply this…genius!

    Please consider adding some information about what I consider one of the most powerful data transformation feature of AdvancedFilter, xlFilterCopy.
    When you place Column Header/Titles in your CopytoRange you can quickly transform your output.
    1. Copy only certain columns from the source data.
    2. Change the Order of the Columns form the source data.
    3. Automatically Pastes Values to conveniently of any formulas.

    Thank You again!

  5. Paul, thank you very much for your excellent guide on copying data with VBA. Your post and training video just like a treasure house for VBA fans. From your website i have learned quite a lot on programing and be able to use it for everyday life. Really appreciate for your hard work and sharing on it. Many thanks!
    BR!
    guchao

    1. Thanks very much Guchao.

      I’m glad you like the resources and article so much. It’s great to hear them thought of as a “treasure house”.

      Paul

  6. Great article Paul.

    In your example of using an array to read faster, you are writing the data back into the spreadsheet one cell at a time. It’s much faster to create an output array containing the filtered data then write this back in one go. This means the code only does the slow bit of interacting with the worksheet twice – once to read and once to write – instead of lots of times.

    Steve

      1. You could use ReDim Preserve to add a element each time through the loop. Alternatively, you could use a single ReDim to dimension the output array to the same size as the input array as you know that it can’t be any bigger, like this:

        ‘ Read through the data
        Dim i As Long, j As Long
        Dim row As Long, Columns As Long
        ‘ Declare the output array variable
        Dim arr_out() As String
        ‘ Set the output array to be the same size as the input array
        ReDim arr_out(UBound(arr, 1), UBound(arr, 2))

        row = 1
        For i = LBound(arr) To UBound(arr)

        ‘ If “Laptop Model A” or header row then copy
        If arr(i, 1) = “Laptop Model A” Or i = 1 Then

        ‘ Copy each column to the output array
        For j = LBound(arr, 2) To UBound(arr, 2)
        arr_out(row, j) = arr(i, j)
        Next j

        ‘ move to the next output row
        row = row + 1

        End If

        Next i

        ‘ Write the output array to the worksheet
        shWrite.Range(“A1”).Resize(UBound(arr_out, 1), UBound(arr_out, 2)).Value2 = arr_out

        Note the above assumes there is an Option Base 1 command at the start of code, otherwise the output is shifted one cell down and to the right.

        This might mean that you end up with a lot of empty rows at the end of the array but it will still be faster as you’ve only got a single write to the worksheet.

        Steve

        1. Hi Steve,

          Thanks for the feedback. I have some fun running the tests and comparing them.

          ReDim Preserve does not work because you can only use it for the Upper Bound(i.e. columns) in a 2D arrays. See this.

          Use the second array is actually slower – but only because of the Resize.

          It takes 840ms for 200,000 records using “For Assign”.
          It takes about 1500ms using the second array. The second array isn’t what slows it down – it is the Range.Resize. If you hard code the assignment instead then it actually comes in around 520ms versus 1450ms using resize.

          Paul

      2. Oops, there was an error in my previous reply! In my example code, arr_out should be dimensioned as an array of variants, not strings:

        Dim arr_out() As Variant

        Otherwise all the formatting is lost when writing it back to the worksheet.

        Steve

  7. Paul

    Thanks for this usefull information. I think my knowledge is better, but what I am still missing is a solution to read characters with an accent, and copy them, like å or é, becourse every time I want to make a copy of these text with these special characters ( for instance When I read file names from a map ) I got an error. Do you have a solution to copy this kind of text?

    Regards

    Karel Hoogeboom

    1. Thanks Karel.

      Sounds like a tricky issue. I haven’t done anything like it in VBA so I’m afraid I don’t have a solution.

      Paul

  8. I started with spreadsheets using Lotus 123 under DOS, but only very recently got into VBA. Your materials are among the clearest and most understandable on the Internet, and this comprehensive post is up to your usual very high standards.
    David Ford
    Timisoara, Romania

  9. Exhaustive. it will take time to read / understand /digest your tutorial.
    one doubt sir.
    copy a row say range(“a10:f10”) from one sheet to update the data available in sh.write
    where ACID of row copied i.e A10 matches ACID of sh.write say range(“a24”).value

  10. Paul, thanks for your detailed posting on vba copy!
    I am working on a project to copy rows from one worksheet to another and was wondering which method is the fastest. I was leaning towards autofilters and your post confirms it is the fastest.
    I love reading your postings and use your debug.assert method from a previous posting for error trapping in all of my projects.
    Thanks again!
    Daryl
    USA

  11. Dear Paul
    Thanks for such comprehensive & useful article on copy-paste/filter.
    I have following questions for you to answer:
    1. What is the “Äll merging conditional formats” in paste special dialogue box & also mentioned by you above?
    2. What are the values (e.g., -4104, 7, etc. mentioned in msdn URL:
    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel
    & how to use these values while copying & pasting data (or how to use them in VBA code?
    Extract from msdn URL:
    Name Value Description
    xlPasteAll -4104 Everything will be pasted.
    xlPasteAllExceptBorders 7 Everything except borders will be pasted.

    1. 1. It merges source\destination Conditional Formatting.
      2. You don’t use the values, you use the constant e.g. xlPasteAll

  12. Hi Paul,
    This is a great and very helpful article to create faster code.
    I like the fact that you showed us so many alternative ways in order to achieve the same goal.
    Thanks for putting so much of your time and effort in it.

  13. In the section ‘Copying and Transposing Data’ your link with `Application.Transpose` is double posted as `https://excelmacromastery.com/excel-vba-copy/#https://excelmacromastery.com/excel-vba-copy/#Assignment_with_Transpose`

  14. Dear Paul
    thanks a lot for your nice post. It is always a pleasure to read them.
    A comment, that kept me awake a part of the night:
    This code will work fine. This is what your wrote in your post

    Option Explicit
    Sub test()
    Dim shIn, shOut As Worksheet
    Set shIn = ThisWorkbook.Worksheets(1)
    Set shOut = ThisWorkbook.Worksheets(2)
    shIn.Range(“A1:C4”).Copy
    shOut.Range(“A1”).PasteSpecial xlPasteValues
    End Sub

    However, if you use Cells to define the range, this is no more working

    Option Explicit
    Sub test()
    Dim shIn, shOut As Worksheet
    Set shIn = ThisWorkbook.Worksheets(1)
    Set shOut = ThisWorkbook.Worksheets(2)
    shIn.Range(Cells(1, 1), Cells(4, 3)).Copy
    shOut.Range(Cells(1, 1), Cells(1, 1)).PasteSpecial xlPasteValues
    End Sub
    You need to activate each worksheet to run it properly

    Option Explicit
    Sub test()
    Dim shIn, shOut As Worksheet
    Set shIn = ThisWorkbook.Worksheets(1)
    Set shOut = ThisWorkbook.Worksheets(2)
    shIn.Activate
    shIn.Range(Cells(1, 1), Cells(4, 3)).Copy
    shOut.Activate
    shOut.Range(Cells(1, 1), Cells(1, 1)).PasteSpecial xlPasteValues
    End Sub

    Funny, isn’t ! May be worth adding a note on this ? Range size is not always known before running the code and using Cells allows defining it when running the code.

    Sincerely yours
    Jeb

    1. Hi Jeb

      shInRange(Cells(1, 1), Cells(4, 3)).Copy

      should be

      shIn.Range(shIn.Cells(1, 1), shIn.Cells(4, 3)).Copy

      You have the specify the range for Cell or otherwise it will default to the active sheet.

      Paul

Leave a Reply

Your email address will not be published. Required fields are marked *