Conditional Copying of cells

Posted by Rob on July 27, 2001 2:42 PM

Hi everyone again :) I was looking for a way to have 2 worksheets in a workbook, and have a few colums/rows of information... What I want to happen is for certain rows in the first sheet (based on a condition in one of the columns) to copy to the next page, but to the next availible cell. So, for example, if i have a colunm in the first sheet called "PAID", and some rows have "X" in them and some dont. On the second sheet, I want all the rows from the first page to copy over that do not have an "X" in the "PAID" column, but I'd like them to be together and not separated by blank cells.

Again, I appreciate any help, you guys are great! thanks.

Posted by Cory on July 27, 2001 10:02 PM

Rob,
I just recorded a macro that did what you described. It may be a long way around, but it works. This code assumes you have your Names are in A and your Xs are in B...

Hope this helped, or at least get you moving in the right direction...

Cory

Posted by Cory on July 27, 2001 10:04 PM

Gues the code would help :_P

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/28/2001 by Cory Ferguson
'
' Keyboard Shortcut: Ctrl+w
'
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="x"
Selection.End(xlDown).Offset(1, 0).Select
Range(Selection, Cells(1)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

Posted by Rob on July 29, 2001 1:24 AM

Thanks!

Macro2 Macro Macro recorded 7/28/2001 by Cory Ferguson Keyboard Shortcut: Ctrl+w Field:=2, Criteria1:="x"