Copy columns if

  • Thread starter Thread starter Kash
  • Start date Start date
K

Kash

Hi, I need to copy columns B, C & H only if Column L = M1 from
sheets("Totals") to sheets("July")

Can some body help me on this pls..
 
IF {condition} then

with Worksheets("Total")
.Columns("B").Copy Worksheets("July").Columns("B")
.Columns("C").Copy Worksheets("July").Columns("C")
.Columns("H").Copy Worksheets("July").Columns("H")
End With

end if

{condition}
I wasn't sure what you meant by
Column L = M1

??
 
Sorry if I wasn't clear..

M1 is date

So, if L10:L30=M1 then I need corresponding rows form columns B, C & H only
to be copied to sheets("Totals") to sheets("July")
 
You can copy adjacent columns with one Copy method call (it is only
noncontiguous ranges that can't be handled by a single Copy method call)...

.Columns("B:C").Copy Worksheets("July").Range("B1")
.Columns("H").Copy Worksheets("July").Range("H1")
 
I tried with below code, but getting 'Type mismatch' error in 1st line


If Worksheets("Totals").Columns("L") = Worksheets("Totals").Range("M2") Then

With Worksheets("Totals")
..Columns("B").Copy Worksheets("Sheet1").Columns("B")
..Columns("C").Copy Worksheets("Sheet1").Columns("C")
..Columns("H").Copy Worksheets("Sheet1").Columns("D")

End With
End If
 
my code was for copying the entire column, given that we were not clear
about your filter.
Now i understand that for each row, if the value in L matches the value in
cell M1, then just that row must be copied.

Using an Autofilter should work

Sub FilterMe()

Range("C1:L1").AutoFilter ' turn on the filter
Selection.AutoFilter Field:=10, Criteria1:=Range("M1")
Range("C:D").Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("C1")
Range("H:H").Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("H1")
Range("C1:L1").AutoFilter ' turn off the filter
End Sub
 
Back
Top