May 25, 2017

Transfer Specific Data to Specific Sheets Automatically

Posted on by Dinesh Kumar Takyar

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

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

  1. Hello Sir,
    when excel vba editor opened then show me a msg box is this possible or not

  2. 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. hi, did you ever find the video showing how to transfer horizontal data to specific sheets or figure out the code?

  3. 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

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

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

  6. 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

  9. Hi Sir,

    Can you please help me with my requirement. I need to push the data of an employee from a particular cell in the master excel sheet(where master excel sheet has n number of employee names) to a particular cell in the individual employee excel sheet. is it possible with macros?

  10. Please sir, am teacher and I am new to vba coding. I want design a userform that can be to input students record in this way.
    1- Classes as workbook
    2- Each workbook have sheets as subjects
    I want the userform to send their scores respectively by choosing a class, and subject from the userform.

Comments are closed.