Copy rows from dataset with data input on one sheet based on another sheet's criteria. [VBA]

Itzybell

New Member
Joined
Jul 15, 2019
Messages
7
Hi all,
I have 3 sheets:
"Input","dataset","Print"

My objective is to input a few data in "input" Column A and run the macro.
If any cell in Column A of "Dataset" = "input" Column A,
it will print out the whole row results to "Print".

I found many examples of copying rows into another sheet,
however, it is mostly all coded based on the cell value.
My cell value will not be constant therefore I want the macro to look based on Column A instead.

Here is how the dataset looks like:
1579796199837.png


I did some research and i did found a VBA code that works after some modification,
but it is taking too long to search every data in the cells.
It is also looking at a limited range in Column A instead of the whole Column A:

VBA Code:
Sub Copy()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Condition As Worksheet


    Set Source = ActiveWorkbook.Worksheets("Dataset")
    Set Target = ActiveWorkbook.Worksheets("Print")
    Set Condition = ActiveWorkbook.Worksheets("Input")

    j = 1    'This will start copying data to Target sheet at row 1
      For Each d In Condition.Range("A5:A86")
        For Each c In Source.Range("A1:A86")
            If d = c Then
                Source.Rows(c.Row).Copy Target.Rows(j)
                j = j + 1
            End If
        Next c
      Next d

 
End Sub
I would appreciate if someone could help me in this!
Many thanks! :)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,668
Office Version
365
Platform
Windows
How about
VBA Code:
Sub Itzybell()
   Dim Cl As Range, Rng As Range
   Dim Source As Worksheet, Target As Worksheet, Condition As Worksheet

   Set Source = ActiveWorkbook.Worksheets("Dataset")
   Set Target = ActiveWorkbook.Worksheets("Print")
   Set Condition = ActiveWorkbook.Worksheets("Input")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Condition.Range("A2", Condition.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Source.Range("A2", Source.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
      If Not Rng Is Nothing Then Rng.EntireRow.Copy Target.Range("A1")
   End With
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,048
Office Version
2013
Platform
Windows
Another
Code:
Sub t()
 Dim Source As Worksheet, Target As Worksheet, Condition As Worksheet, c As Range, fn As Range
 Set Source = ActiveWorkbook.Worksheets("Dataset")
 Set Target = ActiveWorkbook.Worksheets("Print")
 Set Condition = ActiveWorkbook.Worksheets("Input")
    For Each c In Condition.Range("A5", Condition.Cells(Rows.Count, 1).End(xlUp))
        Set fn = Source.Range("A:A").Find(c.Value, , xlValues)
            If Not fn Is Nothing Then
                fn.EntireRow.Copy Target.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,307
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top