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”)
Range(“A1”).Value = “Item”
Range(“B1”).Value = “Unit Price”
Range(“c1”).Value = “Quantity”
Dim r1 As Long, r2 As Long
r1 = 1
r2 = 2
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
Cells(r2, 1).Value = ItemName
Cells(r2, 2).Value = price
Cells(r2, 3).Value = qty
r2 = r2 + 1


Application.ScreenUpdating = True
End Sub
Further Reading:

Published by

Dinesh Kumar Takyar

Welcome to! 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: For a structured Excel VBA training course online you can visit:

2 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

    vikas singh

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.