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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

9 thoughts on “Transfer Specific Data to Specific Sheets Automatically”

  1. hello sir, in this video vertical data transferred to horizontally but how to transfer horizontal data to horizontally with heading like name, date, our price, selling price

  2. Hello Dinesh,
    I have written code to transer row one of data on main sheet to 3 other sheets. I want to know how to move data up on main sheet removing empty row without removing the row which has dropdowns and formatting. Thank you David

  3. there is a problem in range Sheets(Array(“Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”)).Select
    yellow mark is showing. i made 6 sheets in workbook.

  4. Compile error for end if function is found when tried to exactly same. Please tell me solution for that same

  5. hello,thank u I learned so much of vba by ur videos,now I can make an invoice,transfer invoice details to a new work book having colums (date.invno.name.amount)
    in the same manner I cam transfer rcpt also
    with this data now I want to create a separate ledgers for separate customers,in the new workbook.I want to use 1 sheet for one customer I have more customer thanks best regards
    sajid mushtaque

Leave a Reply

Your email address will not be published. Required fields are marked *