How To Auto Transfer Data from Master to Sub Sheets in Excel
On April 10,2022 by Tom Routley
If you are using Excel and would like to automatically transfer data from a master sheet to specified sub sheets when a certain condition is met, this article will explain how to use VBA (Visual Basic for Applications) to do so. This example will demonstrate how to automatically transfer multiple columns in one sheet to another when the value in one of the fields is "Yes". The value of the conditional field can be changed as required.
Autotransfer Data from Master to Sub Sheets in Excel
To get started, open your Excel document and open the Microsoft Visual Basic for Applications window by hitting [Alt] + [F11].
Next, find your sheets in the left column and double-click on the Master. The sheet names will depend on what you have named them in Excel. The Master sheet is the primary sheet where the data is first entered, and the Sub sheet is the location where the data will be populated. Next, paste the following code in the large white field:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("G")) Is Nothing Then Exit Sub 'When there is no change in 'column G then do nothing. 'The cell value that was changed in column G will now be referred to as Target. If Target.Value = "Yes" Then 'When condition is met ("Yes" in column G) then Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Copy _ Sheets(Target.Offset(0, -1).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 'Copy the row where the change is made from column A till column G. 'Paste to the sheet mentioned in column F and to the first available row. End If End Sub
Now, you can close the Microsoft Visual Basic for Applications window and your file will now be ready to automatically transfer data entered on the master sheet to the selected sub sheets. Remember that the Macro will only run when a change is made in column G.
Image: © 123RFom
Leave a Reply