Copying Data from One Sheet to Another with VBA
Copying Data into Another Sheet at the First Blank Row
Copying data with VBA is a common process solved with well written code. Lets say we want to copy data from Sheet1 to Sheet2 with the help of code on a regular basis.
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.
The (2) in Red at the end is the part of the code which offsets to the first blank row in Column A.
If we want to include the starting sheet in the copy procedure the following is another way to copy data from one sheet to another.
In the above example Sheet1 is the worksheet code name for the start sheet that is being copied and Sheet2 is the worksheet code name for the sheet we are copying to.
Worksheet Code Name
The worksheet code name is the code name for the worksheet that you are copying. It is not as heavily used as it should be but here is the advantage, if your worksheet name is changed the code will not break. In the example on the left - Sheet1 is the worksheet code name for a sheet called Data and Sheet2 is the code name for a sheet called Consolidate. If either sheet name is changed provided the worksheet code name is used the code will remain stable. Always use the worksheet code name in VBA.
Other Copy Methods with VBA
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.Sub UseAnArray() 'Excel VBA to copy data
Dim ar As Variant
Sheet2.Range("A11").Resize(UBound(ar), 7)=ar
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:
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:
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.
Sub FindthenCopy() 'Excel VBA find the position of a header
Dim fn As Long
Range(Cells(2, fn), Cells(Rows.Count, fn).End(xlUp)).Copy Sheet4.Range("A65536").End(xlUp)(2)
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.
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;
Sub Test()
Dim i As Integer
Application.ScreenUpdating=False
Sheets("Sheet2").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues
Sheets("Sheet1").Select
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;
Sub Filter1() 'Excel VBA to use the autofilter then copy
Range("A1:A101").Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
Range("A1").Autofilter 'Off with the autofiltter
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.
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.
Rows(Range("A" & Rows.Count).End(xlUp).Row + 1).Insert xlDown
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