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
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(“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”
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
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
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
Hello Sir,
when excel vba editor opened then show me a msg box is this possible or not
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
This is exactly what I’m demonstrating in at least 10 videos. Please do a search on this site.
hi, did you ever find the video showing how to transfer horizontal data to specific sheets or figure out the code?
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
there is a problem in range Sheets(Array(“Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”)).Select
yellow mark is showing. i made 6 sheets in workbook.
Compile error for end if function is found when tried to exactly same. Please tell me solution for that same
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
How to solve auto update links disabled error in excell