VBA Copy From Columns If Value Equal

[ir121973]

Hi, I wonder whether someone could help me please.

I've put together the code below which copies data from non sequential columns.

Dim lRow, lCol As Integer


Sheets("Amalgamation of Search").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column


For Each cell In Range(Cells(, "C"), Cells(1, lCol))
   With .Cells(lRow, "C")
    If .Value = "SC" Then
    Union(Range("A1:A" & lRow), Range("C1:C" & lRow), Range(Cells(1, cell.Column), Cells(lRow, cell.Column))).Copy
    Workbooks.Add
    Range("A1").PasteSpecial
    ActiveWorkbook.SaveAs Filename:= _
    "\\c\s\CAF1\Digital Delivery Group\DDCOPS\Data Security\SC Data.xls"
    ActiveWorkbook.Close
    End With
Next cell


Application.CutCopyMode = False
End Sub

The code works except for this piece of code

   With .Cells(lRow, "C")
    If .Value = "SC" Then

What I'm trying to say is if the value = "SC" then I want it to copy the data, but when I run this I receive a "Invalid or unqualified reference" higlighting this line as the cause

With .Cells

Could someone perhaps look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

 

[Trevor G]

Try removing the . before the word Cells

 

[ir121973]

Hi Trevor G, thank you for this, but unfortunately, although I don't receive an error message, it doesn't copy any data.

Many thanks

Chris

 

[Trevor G]

Try this then, change

If .Value = "SC" Then

To

If cell.Value = "SC" Then