MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying row if value in column G is greater than 0.


Posted by Ben on October 20, 2000 1:37 PM

How do I copy entire row's in a table to another worksheet only if the value in column G of the row is greater than 0?

!!Using a Macro!!


Posted by thomas venn on October 23, 2000 1:35 PM

first, you need two sheets, named Sheet1 and Sheet2. your source data in column G is in Sheet1,
'and column H in sheet 1 is blank (for now, but not after you run the macro). Sheet2 is where
'the data is being pasted to.

' cheers,

'Thomas


Sub Macro1()
'
Application.Goto Reference:="R1C1"
'go to cell A1
Application.Goto Reference:="R1C8"
'go to cell H1
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,""copy"",""do not cpy"")"
Selection.Copy
ActiveCell.Range("A1:A313").Select
'copies the cell from cell H1 to H313. your can change this if your range is bigger.
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
Calculate
End Sub

Sub Macro2()
Application.Goto Reference:="Sheet1!R1C1"
Application.Goto Reference:="R1C8"
Cells.Find(What:="copy", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
Selection.ClearContents
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:IU1").Select
Selection.Copy
Application.Goto Reference:="Sheet2!R1C1"
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
Application.Goto Reference:="Sheet1!R1C1"


Application.Goto Reference:="Sheet1!R1C1"
Application.Goto Reference:="R1C8"
Cells.FindNext(After:=ActiveCell).Activate
Application.CutCopyMode = False
Selection.ClearContents
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Range("A1:IU1").Select
Selection.Copy
Application.Goto Reference:="Sheet2!R1C1"
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(0, 6).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
Application.Goto Reference:="Sheet1!R1C1"


Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"
Application.CutCopyMode = False
Application.Run "Macro2"



End Sub

Posted by Ben on October 26, 2000 12:05 PM

Cheers!!

Cheers!!