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 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”)
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
Loop
Application.ScreenUpdating = True
End Sub