Copying Data from One Sheet to Another with VBA

Copying Data into Another Sheet at the First Blank Row
The following Excel VBA procedure copies data from the active sheet and pastes it in the first blank cell at the bottom of a range in another worksheet.

Sub LrNoVariant() 'Add to data on destination sheet.
Range("A11", Range("G" & rows.count).End(xlUp)).Copy Sheet2.Range("A65536").End(xlUp)(2)
End Sub

The (2) in Red at the end is the part of the code which offsets to the first blank row in Column A.

In VBA there are many ways to do the same things and the following will replicate the above. It looks less efficient however it should work more quickly than copying data for larger data sets.

Option Explicit

Sub UseAnArray() 'Excel VBA to copy data
Dim ar As Variant

Sheet2.Range("A11").Resize(UBound(ar), 7)=ar
End Sub

More can be learnt about using array variants in The VBA Array article.

The following is a useful technique for ranges which are non continuous but of the same size. By same size I mean are the same depth. Study the following example:

Sub CopyRngSameSize() 'Excel VBA copy a range
[A1:A3, C1:D3].Copy Sheet2.[a1]
End Sub

The above will skip column B and will paste data from A1 to Ac and C1 to D3 to sheet 2 range A1. The trick when copying data like this is to ensure the range size is the same for each segment. [A1:A3, C1:C3] both ranges end in row 3. This can be extended to add more ranges. For example:

Sub CopyRngSameSize() 'Excel VBA copy a static range
[A1:A3, C1:D3, F1:M3,T1:T3,V1:Z3].Copy Sheet2.[a1]
End Sub

As you can see the Excel range can grow and as long as the range is the same size. Rows 1 to 3 are repeated above and while this is true you can continue to place more ranges in the Excel square brackets.

Trap Headings

Occasionally you will have headings moving around from period to period based on a downloaded dataset. In this instance you could incorporate a find routine to trap the location for specific words. The following will find the location of Header1.

Option Explicit
Sub FindthenCopy() 'Excel VBA find the position of a header
Dim fn As Long

fn=Rows("1:1").Find("Header 1").Column
Range(Cells(2, fn), Cells(Rows.Count, fn).End(xlUp)).Copy Sheet4.Range("A65536").End(xlUp)(2)

End Sub

After Header 1 is found, the VBA procedure will copy the data from the found column to the first blank row in Column A of sheet2.

Copying Data that Meets Criteria

Excel users are a pragmatic bunch and grow up using the IF statement in every day Excel use. So when it comes to VBA one naturally assumes the best way to move data from one place to another is by testing each cell to see if it meets a condition then moving the whole row. The following is a common occurrence of code which I see on excel forums;

Option Explicit
Sub Test()
Dim i As Integer


For i=2 To 101
If Range("B" & i).Value="Ford" Then
Range("B" & i).EntireRow.Copy
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues
End If
Next i
End Sub

I have probably put in a few too many lines but I made the assumption the recorder was used to generate the bulk of the code. The problem with the VBA above is that as the list of information grows exponentially larger, the time the process takes to run slows according to the size of the dataset. If the list is 100 lines strong, then Excel has to perform 100 actions if all items meet the criteria. It could be argued that in the above example there are 200 things for Excel to do, ask the question 100 times, perform the action potentially a further 100 times.

A Simple Solution

It is easier to ask one Excel question and perform one Excel action for the whole dataset. This can be done by drawing on prior lessons coved in the Autofiler with VBA section of the site. In the example above a very simple way to move the data would be the following VBA construct;

Option Explicit
Sub Filter1
() 'Excel VBA to use the autofilter then copy
Range("A1:A101").AutoFilter 1, "Ford"
Range("A1:A101").Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
Range("A1").Autofilter 'Off with the autofiltter
End Sub

This method is useful when you want to push information into a summary sheet on a regular basis. The VBA isolates like data and moves the information seamlessly between sheets.

Insert a Row at the Bottom of a Range and Copy Formula from Above

If you have new data appended to the bottom of a range of cells and need the formula from the cells above copied down, then the following Excel VBA code will address this situation. It copies the last row of data, inserts a new row and the formulas from above will be transposed down one row in perfect sequence.

Sub CopyDown() 'Excel VBA to copy data from the Cells above.
Rows(Range("A" & Rows.Count).End(xlUp).Row).Copy
Rows(Range("A" & Rows.Count).End(xlUp).Row + 1).Insert xlDown
End Sub

This concept is taken further in the following article where you specify in the cells how many times the row above is to be copied. Copy n Rows in Excel