Transfer Specific Data from Sheet1 to Sheet2

How to use IF in a looping process to transfer specific data from sheet1 to sheet2 quickly, easily and automatically.

Watch the training video before studying the VBA code given below:

Watch this video on YouTube.

Here’s the complete code to transfer specific data from one Excel worksheet to another automatically.
Sub Looping()
Application.ScreenUpdating = False
Dim sh2 As Worksheet
Dim sh1 As Worksheet
Dim ItemName As String
Dim Price As Long
Dim Qty As Long

Dim r1 As Long, r2 As Long
r1 = 1
r2 = 2

    Set sh1 = Sheets(“sheet1”)
Set sh2 = Sheets(“sheet2”)

sh2.Activate
Range(“A1”).Value = “Item”
Range(“B1”).Value = “Unit Price”
Range(“C1”).Value = “Quantity”

sh1.Activate

Do While Cells(r1, 1) <> “”

ItemName = Cells(r1, 2)
r1 = r1 + 1
Price = Cells(r1, 2)
r1 = r1 + 1
Qty = Cells(r1, 2)
r1 = r1 + 1

If ItemName = “A” Or ItemName = “a” Then
sh2.Activate
Cells(r2, 1).Value = ItemName
Cells(r2, 2).Value = Price
Cells(r2, 3).Value = Qty
r2 = r2 + 1
Cells(r2, 1).Select
End If

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

One thought on “Transfer Specific Data from Sheet1 to Sheet2”

  1. Kindly help with VB codes with this excel problem

    I have I have two worksheet. Sheet1 and Sheet2.
    They both has 8×15 data table with same headings, but, sheet2 table is a pivot table.
    Constantly Sheet1 is populated and will transfer the data to next available space on Pivot table in Sheet 2 using VB Code.
    Sheet2 represent the Database hence must be protected with password or hid from user.

    Thanks

    Samson Edunjobi

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.