Copy columns if

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..
 
P

Patrick Molloy

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

??
 
K

Kash

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")
 
R

Rick Rothstein

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")
 
K

Kash

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
 
P

Patrick Molloy

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
 
Top