Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells when column contains data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells when column contains data
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells when column contains data
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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells when column contains data
Kirk,
Something like: With ActiveCell.CurrentRegion .AutoFilter Field:=2, Criteria1:="<" .SpecialCells(xlCellTypeVisible).Range("A:B").Copy _ Worksheets("Sheet2").Range("A1") .SpecialCells(xlCellTypeVisible).Range("D:D").Copy _ Worksheets("Sheet2").Range("C1") .AutoFilter End With HTH, Bernie MS Excel MVP "Kirk A" wrote in message ... 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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells when column contains data
Thank you.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Kirk, Something like: With ActiveCell.CurrentRegion .AutoFilter Field:=2, Criteria1:="<" .SpecialCells(xlCellTypeVisible).Range("A:B").Copy _ Worksheets("Sheet2").Range("A1") .SpecialCells(xlCellTypeVisible).Range("D:D").Copy _ Worksheets("Sheet2").Range("C1") .AutoFilter End With HTH, Bernie MS Excel MVP "Kirk A" wrote in message ... 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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy following cells in the same column | Excel Discussion (Misc queries) | |||
How to copy values available in different cells of a column into d | Excel Discussion (Misc queries) | |||
Macro, Copy Selected Cells Down a Column | Excel Discussion (Misc queries) | |||
How can i copy a group of cells in a row to a column? | Excel Discussion (Misc queries) | |||
Copy cells to another column with VBA | Excel Discussion (Misc queries) |