Copy cells when column contains data

[Kirk A]

I need a macro that would copy the information in a range of cells only if
there is a value entered in a column of the same row.

Do I need a macro to do this? If I do, could someone show me where to
start writing it?


Thanks

 

[Bernie Deitrick]

Kirk,

You can use the Autofilter method: this example will copy a row only if the
second column of the activecell's currentregion is filled, then will copy to
below the last entry of column A of the same sheet, leaving three blank rows
between:

With ActiveCell.CurrentRegion
.AutoFilter Field:=2, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy Range("A65536").End(xlUp)(5)
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP

 

[Kirk A]

I have several columns of data. How would I make this example copy the data
in the first, second and fourth column?

I have added a line to the macro to place the data on a new worksheet, but
it adds the three blank lines at the top of the sheet. How do I keep from
adding these lines?

Thanks for you help.
Kirk

 

[Bernie Deitrick]

Kirk,

Something like:

With ActiveCell.CurrentRegion
.AutoFilter Field:=2, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Range("A:B").Copy _
Worksheets("Sheet2").Range("A1")
.SpecialCells(xlCellTypeVisible).Range("D").Copy _
Worksheets("Sheet2").Range("C1")
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP

 

[Kirk A]

Thank you.

Kirk,

Something like:

With ActiveCell.CurrentRegion
.AutoFilter Field:=2, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Range("A:B").Copy _
Worksheets("Sheet2").Range("A1")
.SpecialCells(xlCellTypeVisible).Range("D").Copy _
Worksheets("Sheet2").Range("C1")
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP