Forum Discussion

clh14_96's avatar
clh14_96
Copper Contributor
Feb 16, 2024
Solved

VBA to search for project code within column and paste rows that match into different sheet

Hi all,   I'm looking to write a VBA macro that basically looks up the project code 123 in column D in a worksheet called "Paste into here" and for every line where the project code 123 matches a r...
excel
Formulas and Functions
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Feb 16, 2024

    clh14_96 

    Try this:

    Sub Load_File()
    Dim LoadWS As Worksheet
    Dim DataWS As Worksheet
    Dim ProjectField As Range
    Application.ScreenUpdating = False
    Set LoadWS = Worksheets("Load File")
    LoadWS.Cells.ClearContents
    Set DataWS = Worksheets("Paste into here")
    Set ProjectField = DataWS.Range("D8", DataWS.Range("D8").End(xlDown))
    ProjectField.AutoFilter Field:=1, Criteria1:="123"
    ProjectField.EntireRow.Copy Destination:=LoadWS.Range("A1")
    ProjectField.AutoFilter
    Application.ScreenUpdating = True
End Sub
HansVogelaar's avatar
HansVogelaar
MVP
Feb 16, 2024

clh14_96 

Try this:

Sub Load_File()
    Dim LoadWS As Worksheet
    Dim DataWS As Worksheet
    Dim ProjectField As Range
    Application.ScreenUpdating = False
    Set LoadWS = Worksheets("Load File")
    LoadWS.Cells.ClearContents
    Set DataWS = Worksheets("Paste into here")
    Set ProjectField = DataWS.Range("D8", DataWS.Range("D8").End(xlDown))
    ProjectField.AutoFilter Field:=1, Criteria1:="123"
    ProjectField.EntireRow.Copy Destination:=LoadWS.Range("A1")
    ProjectField.AutoFilter
    Application.ScreenUpdating = True
End Sub

Resources