VBA code to cope whole row if is matching value in column - can't figure out

[primeprime]

Hello All,
I want ask you for your help.
I have some data that I need to sort depends of the value in Column G. If value in row is matching "x" value in column G, copy whole row to another sheet.
I tried to modify some VBA codes from internet but no success.
Thanks in advance for you help.

 

[HaHoBe]

Hi primeprime,

the values you want to search for are in Column F, not Column G.

This code deletes any contents in the target sheets Sheet1 and Sheet2, uses the autofilter to copy the visible ranges over:

Public Sub MrE_1233446_170540C()
' https://www.mrexcel.com/board/threads/vba-code-to-cope-whole-row-if-is-matching-value-in-column-cant-figure-out.1233446/
Dim wsData As Worksheet
Dim varWhat As Variant
Dim varItem As Variant
Dim varSheets As Variant

varWhat = Array("prime", "tomcat")
varSheets = Array("Sheet1", "Sheet2")

Set wsData = Worksheets("Data")
With wsData
  If .AutoFilterMode Then .AutoFilterMode = False
  For varItem = LBound(varWhat) To UBound(varWhat)
    .Range("A1").CurrentRegion.AutoFilter field:=6, Criteria1:=varWhat(varItem)
    If .Cells(.Rows.Count, "A").End(xlUp).Row > 1 Then
      Worksheets(varSheets(varItem)).UsedRange.Clear
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      Worksheets(varSheets(varItem)).Paste
    End If
  Next varItem
  .AutoFilterMode = False
End With
Set wsData = Nothing
End Sub

Code should be added to a standard module.

Ciao,
Holger

 

[primeprime]

Hi primeprime,

the values you want to search for are in Column F, not Column G.

This code deletes any contents in the target sheets Sheet1 and Sheet2, uses the autofilter to copy the visible ranges over:

Public Sub MrE_1233446_170540C()
' https://www.mrexcel.com/board/threads/vba-code-to-cope-whole-row-if-is-matching-value-in-column-cant-figure-out.1233446/
Dim wsData As Worksheet
Dim varWhat As Variant
Dim varItem As Variant
Dim varSheets As Variant

varWhat = Array("prime", "tomcat")
varSheets = Array("Sheet1", "Sheet2")

Set wsData = Worksheets("Data")
With wsData
  If .AutoFilterMode Then .AutoFilterMode = False
  For varItem = LBound(varWhat) To UBound(varWhat)
    .Range("A1").CurrentRegion.AutoFilter field:=6, Criteria1:=varWhat(varItem)
    If .Cells(.Rows.Count, "A").End(xlUp).Row > 1 Then
      Worksheets(varSheets(varItem)).UsedRange.Clear
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      Worksheets(varSheets(varItem)).Paste
    End If
  Next varItem
  .AutoFilterMode = False
End With
Set wsData = Nothing
End Sub

Code should be added to a standard module.

Ciao,
Holger

This is working perfectly !! Thank you so much for your help.
Is there any chance you could also help me with conditional formating?
In column "D - Updated" I will put date and I want that whole row will be colored if:
- Column D is older then Today - color blue
- Column D is older then Yesterday and more - color red
- Column D equal Today - color green.

I tried to do it with conditional formating without VBA but not big success

 

[primeprime]

Till now I was able to make formating just with one condition, but I don't know how to add another conditional.

Sub formating()


'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-24
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2<TODAY()"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll Down:=-3
End Sub

 

[primeprime]

Till now I was able to make formating just with one condition, but I don't know how to add another conditional.

Sub formating()


'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-24
    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2<TODAY()"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll Down:=-3
End Sub

I've solved this on my way.

 

[primeprime]

Hi primeprime,

the values you want to search for are in Column F, not Column G.

This code deletes any contents in the target sheets Sheet1 and Sheet2, uses the autofilter to copy the visible ranges over:

Public Sub MrE_1233446_170540C()
' https://www.mrexcel.com/board/threads/vba-code-to-cope-whole-row-if-is-matching-value-in-column-cant-figure-out.1233446/
Dim wsData As Worksheet
Dim varWhat As Variant
Dim varItem As Variant
Dim varSheets As Variant

varWhat = Array("prime", "tomcat")
varSheets = Array("Sheet1", "Sheet2")

Set wsData = Worksheets("Data")
With wsData
  If .AutoFilterMode Then .AutoFilterMode = False
  For varItem = LBound(varWhat) To UBound(varWhat)
    .Range("A1").CurrentRegion.AutoFilter field:=6, Criteria1:=varWhat(varItem)
    If .Cells(.Rows.Count, "A").End(xlUp).Row > 1 Then
      Worksheets(varSheets(varItem)).UsedRange.Clear
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
      Worksheets(varSheets(varItem)).Paste
    End If
  Next varItem
  .AutoFilterMode = False
End With
Set wsData = Nothing
End Sub

Code should be added to a standard module.

Ciao,
Holger

Could you help me to edit this code so data will be copied to other sheets with keeping the same size of columns,rows as in the main Data sheet?

 

[HaHoBe]

Hi primeprime,

sorry I do not understand what you mean by keeping the same size of columns,rows as in the main Data sheet? To me this could mean copy over the full range from the data sheet as by narrowing down the range by filters will definitely not result in the same number of rows. Or do you mean to add more criterias than exisiting? For that you would need to add items to the arrays both for the criteria (varWhat) as well as for the target sheet (varSheets) after the exisitng values.

Ciao,
Holger