May 23, 2017

Transfer Data from Sheet1 to Sheet2

How to transfer data from Sheet1 to Sheet2 using a looping process without the offset function. Today we learn again how to use a ‘do while’ loop and about variables.

Watch the training video and then study the VBA code step by step:

Watch this video on YouTube.

Here’s the complete VBA code to transfer data from one worksheet to another:

Sub transferData()
Application.ScreenUpdating = False
Dim sh1 As Worksheet, sh2 As Worksheet
Dim ItemName As String
Dim price As Long, qty As Long
Set sh1 = Sheets(“Sheet1”)
Set sh2 = Sheets(“Sheet2”)
sh2.Activate
Range(“A1”).Value = “Item”
Range(“B1”).Value = “Unit Price”
Range(“c1”).Value = “Quantity”
Dim r1 As Long, r2 As Long
r1 = 1
r2 = 2
sh1.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
sh2.Activate
Cells(r2, 1).Value = ItemName
Cells(r2, 2).Value = price
Cells(r2, 3).Value = qty
r2 = r2 + 1

sh1.Activate

Loop
Application.ScreenUpdating = True
End Sub
Further Reading:

3 thoughts on “Transfer Data from Sheet1 to Sheet2

  1. hi sir ia have a backend tool where i search status of perticular unique id and fetch data in excel like address i want automatic fetch data from website if its possible please make a vba ciode

    regards
    vikas singh

  2. Hello Sir,

    I need to save data from 1 Sheet To 2 Sheet but not want to enter Duplicate Entries.
    I am not able to do the same this is my VB Code.
    Sub Vaibhav_Save()
    Dim X As Long
    Dim Y As Worksheet
    Set Y = Sheet2
    X = Y.Range(“B” & Rows.Count).End(xlUp).Row + 1
    With Y
    .Cells(X, 2).Value = Sheet5.Range(“D8”).Value
    .Cells(X, 3).Value = Sheet5.Range(“D10”).Value
    .Cells(X, 4).Value = Sheet5.Range(“D12”).Value
    .Cells(X, 5).Value = Sheet5.Range(“D14”).Value
    .Cells(X, 6).Value = Sheet5.Range(“D16”).Value
    .Cells(X, 7).Value = Sheet5.Range(“D17”).Value
    .Cells(X, 8).Value = Sheet5.Range(“D18”).Value
    .Cells(X, 9).Value = Sheet5.Range(“D19”).Value
    .Cells(X, 10).Value = Sheet5.Range(“D20”).Value
    .Cells(X, 11).Value = Sheet5.Range(“D21”).Value
    .Cells(X, 12).Value = Sheet5.Range(“D22”).Value
    .Cells(X, 13).Value = Sheet5.Range(“D24”).Value
    .Cells(X, 14).Value = Sheet5.Range(“D25”).Value
    .Cells(X, 15).Value = Sheet5.Range(“D26”).Value
    .Cells(X, 16).Value = Sheet5.Range(“D27”).Value
    .Cells(X, 17).Value = Sheet5.Range(“D28”).Value
    .Cells(X, 18).Value = Sheet5.Range(“D29”).Value
    .Cells(X, 19).Value = Sheet5.Range(“N8”).Value
    .Cells(X, 20).Value = Sheet5.Range(“N9”).Value
    .Cells(X, 21).Value = Sheet5.Range(“N10”).Value
    .Cells(X, 22).Value = Sheet5.Range(“N11”).Value
    .Cells(X, 23).Value = Sheet5.Range(“P11”).Value
    .Cells(X, 24).Value = Sheet5.Range(“R11”).Value
    .Cells(X, 25).Value = Sheet5.Range(“N12”).Value
    .Cells(X, 26).Value = Sheet5.Range(“N13”).Value
    .Cells(X, 27).Value = Sheet5.Range(“N14”).Value
    .Cells(X, 28).Value = Sheet5.Range(“P14”).Value
    .Cells(X, 29).Value = Sheet5.Range(“R14”).Value
    .Cells(X, 30).Value = Sheet5.Range(“N15”).Value
    .Cells(X, 31).Value = Sheet5.Range(“N16”).Value
    .Cells(X, 32).Value = Sheet5.Range(“N17”).Value
    .Cells(X, 33).Value = Sheet5.Range(“N18”).Value
    .Cells(X, 34).Value = Sheet5.Range(“Q18”).Value
    .Cells(X, 35).Value = Sheet5.Range(“L21”).Value
    .Cells(X, 36).Value = Sheet5.Range(“N21”).Value
    .Cells(X, 37).Value = Sheet5.Range(“P21”).Value
    .Cells(X, 38).Value = Sheet5.Range(“R10”).Value
    .Cells(X, 39).Value = Sheet5.Range(“R13”).Value
    .Cells(X, 40).Value = Sheet5.Range(“N27”).Value
    .Cells(X, 300).Value = Sheet5.Range(“D31”).Value

    End With
    ThisWorkbook.Save
    MsgBox “ENTRY SAVE SUCCESSFULLY”
    Sheet5.Range(“D8:D30”) = “”
    Sheet5.Range(“N8:N18”) = “”
    Sheet5.Range(“R10”) = “”
    Sheet5.Range(“P14”) = “”
    Sheet5.Range(“Q18”) = “”
    Sheet5.Range(“R18”) = “”
    Sheet5.Range(“L21”) = “”
    Sheet5.Range(“R13”) = “”
    Sheet5.Range(“R17”) = “”
    Sheet5.Range(“D31”) = “”

    End Sub

Comments are closed.