Hi, I have a VBA that works perfect moving selected data from column A to a new sheet. I want to use this VBA in a new workbook, but the data I want is in column X and I can't figure out how to change it to X. So in the source sheet(BRO_20190910) if column X contains the word ECO, I want to move the whole row to the destination sheet(ECO).
This is the original code, thank you for any assistance.
This is the original code, thank you for any assistance.
Code:
Public Sub MoveParty()Sheets("ECO").Select
Cells.Select
Selection.ClearContents
Sheets("BRO_20190910").Select
Dim c As Range, CopyRange As Range, DataRange As Range
Dim DestRange As Range
Dim Lr As Long
With ThisWorkbook
With .Sheets("BRO_20190910") 'source sheet
Lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set DataRange = .Range(.Cells(1, 1), .Cells(Lr, 1))
End With
With .Sheets("ECO") 'destination sheet
Lr = IIf(IsEmpty(.Range("A1").Value), 1, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
Set DestRange = .Cells(Lr, 1)
End With
End With
DataRange.EntireRow.Hidden = False
For Each c In DataRange.Cells
If c.Value = "ECO" Then
If CopyRange Is Nothing Then
Set CopyRange = c
Else
Set CopyRange = Union(CopyRange, c)
End If
End If
Next c
If Not CopyRange Is Nothing Then
With CopyRange.EntireRow
.Copy DestRange
.Delete shift:=xlShiftUp
End With
End If
End Sub