Transfer Specific Data to Specific Sheets Automatically

How to transfer specific data to specific sheets automatically from master sheet using VBA.

Watch the training video before studying the VBA code:

watch this video on YouTube.

Here’s the complete VBA code to transfer data from a master sheet to specific worksheets:

Sub transferData()
Application.ScreenUpdating = False
‘Dim myData As Worksheet, ItemA As Worksheet, ItemB As Worksheet, ItemC As Worksheet, ItemD As Worksheet, ItemE As Worksheet
Dim ItemName As String
Dim price As Long, qty As Long
Dim r1 As Long, erow As Long
r1 = 1
Sheets(Array(“Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”)).Select
Sheets(“Sheet2”).Activate
Cells.Select
Selection.ClearContents
Sheets(Array(“Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”)).Select
Sheets(“Sheet2”).Activate
Range(“A1”).Select
ActiveCell.Value = “Item”
Range(“B1”).Select
ActiveCell.Value = “Price”
Range(“C1”).Select
ActiveCell.Value = “Quantity”

myData.Activate

Do While Cells(r1, 1) <> “”
ItemName = Cells(r1, 2).Value
r1 = r1 + 1
price = Cells(r1, 2).Value
r1 = r1 + 1
qty = Cells(r1, 2)
r1 = r1 + 1
p = Worksheets.Count
For q = 1 To p
If ActiveWorkbook.Worksheets(q).CodeName = UCase(ItemName) Then
Worksheets(q).Activate
erow = Worksheets(q).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = ItemName
Cells(erow, 2).Value = price
Cells(erow, 3).Value = qty
End If
Next q
myData.Activate
Loop
Application.ScreenUpdating = True
End Sub