Introduction

During your day-to-day use of Excel, you might end up needing to copy row data from one worksheet to another worksheet. To allow greater flexibility for a user to choose which worksheet the row data is to be copied to, we will introduce a selection criteria technique in this tutorial.

Scenario:

Suppose the user has a workbook which contains 5 worksheets with names “DATA”, “FUEILOIL”, “GASOIL”, ULSD” and “KERO” respectively. The “Data” worksheet is a master sheet which contains all relevant data rows to be copied to other sheets, while “FUEILOIL”, “GASOIL”, ULSD” and “KERO” worksheets are recipient sheets to receive these data rows.

The key focus in this tutorial, is to make use of a copy-to-destination criterion column G on “DATA” worksheet, to let the user indicate which worksheets should the corresponding data rows be copied to. After the user inputs the criterion worksheet names manually under column G, the job will get done by means of VBA code.

Before we proceed, have a look below at what our “Data” worksheet looks like. Here we assume all 5 worksheets share the same headers “Order No.”, “Date”, ”Item”, ”Price”, “Quantity” and “Notes” from A1:F1, except that there is 1 extra column header named “Copy-To-Criterion ( Worksheet Name )” on “Data” worksheet.

• Worksheets(“DATA”)

• Worksheets(“FUELOIL”)

Sub CopyRow_UponCriteria()
 Application.ScreenUpdating = False
 Application.CutCopyMode = False
 Dim ws As Worksheet
 Dim src_ws As Worksheet
 Dim Cell As Range
 Dim wsName As String
 Set src_ws = Worksheets("DATA")
 eRow_src = src_ws.Range("G" & Rows.Count).End(xlUp).Row
 For r = eRow_src To 2 Step -1
 With src_ws
 If .Cells(r, 7).Value <> "" Then
 wsName = .Cells(r, 7).Value
 eRow = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row
 .Range(.Cells(r, 1), .Cells(r, 6)).Copy Worksheets(wsName).Range("A" & eRow + 1)
 .Range(.Cells(r, 1), .Cells(r, 6)).EntireRow.Delete
 End If
 End With
 Next
 Application.CutCopyMode = True
End Sub


VBA Code Analysis:



#002
This ScreenUpdating line intends to speed up the VBA execution efficiency by turning off the screen updating feature of Excel. This is particularly useful when the data rows to be copied are gigantic in amount.


#003
By turning off CutCopyMode, it avoids the user to view the strange dotted line triggered during the Excel-copying process


#008
Here an object variable named “src_ws” is used to refer to worksheets(“DATA”) object.


#009
Here an number variable named “ eRow_src” is used to store the row number of the last occupied cells under column G on worksheets(“DATA”). This prepares for our looping later on.


#010
The for-loop here starts with last occupied row under column G first, and then loop backward to the initial row 2 by using Step -1. At first glance, it may seem oddly to some but the reason behind is justifiable. As in this task, we would also delete the whole data row after it has been successfully copied to other worksheets. This deletion process, however, will trigger any other untouched rows beneath to move upwards. In this way, the originally pattern of destined row numbers for VBA to loop through will be disrupted, if we deem to loop in a normal forward approach. To avoid it, looping backward can be one of the solutions.


#012
The line is to make sure that the criterion range does not contain empty worksheet name. Row-copying process will then activate as long as the criterion cell under column G is not text-empty.


#014
The line intends to setup a number variable named “eRow” to retrieve the row number of last occupied cell in corresponding recipient worksheet. This helps to locate the next row number the data row should be copied to later.


#015
The line makes use of 2 embedded cell objects within a range object, to limit the column of a targeted copy row from column A to F. ( Hint: In Excel, column A takes a numeric column value of 1, while column F takes a numeric column value of 6). After that, a copy method is introduced to copy the current row under looping. The right hand side of the copy method contains a destination parameter to let VBA identify which worksheet, and which cell or row should the copied data row be pasted to. As you can see, eRow has to be added up by 1, such that a new blank row is ready to be received the copied data row.


#016
After the data row is copied, we use EntireRow to extend our current row from column A – F to the truly entire row to be deleted. Without doubt, during our coding on #15, we can also extend our range to be copied using EntireRow. However, we simply do not do so because of efficiency consideration, i.e. our table only contains a few headers.


#020
After the looping finishes it job, we then let the dotted line for normal Excel copying back to its normal visibility.



Attached below are pictures of finished screenshots.

Worksheets(“DATA”)



Worksheets(“FUELOIL”)



Worksheets(“GASOIL”)


Conclusion:
As a quick re-cap, the pivotal key in this tutorial is to introduce a column for the user to decide and input manually which worksheets are for the relevant data rows to be transmitted. On the realm of VBA coding, as there will be row deletion after the data row is successfully copied, we have to utilize the approach of a backward for-loop to avoid complications during row deletion. And always remember to check whether we should add extra 1 row for the destination to be copied to.
Related References:
If you would like to further advance the manipulation of worksheets objects in Excel VBA, such as different ways of worksheet referencing, creation, naming, copying, insertion, etc., you may visit our other link in the following: https://software-solutions-online.com/excel-vba-working-with-sheets/
If you are to retrieve data from another workbook, instead of within a worksheet, you may integrate the techniques from this tutorial with the following:
https://software-solutions-online.com/excel-vba-get-data-from-another-workbook-through-automation/