Hello,
I am using Excel 2007.
I need a macro that will cut and paste certain cells from a row based on a cell value in that row. What is cut will be pasted to a different sheet based on the cell value. The original row will be deleted and removed.
Source Sheet is "APPLICATIONS"
Destination Sheet 1 is "FUNDINGS"
Destination Sheet 2 is "DENIALS"
If the value in Column H is "Funded" Move columns A:X from active row to first empty row on destination sheet 1.
If the value in Column H is "Denied" Move columns A:X from active row to first empty row on destination sheet 2.
1. Source sheet has headings in row 1 and 2 so it will start checking the data in row 3.
2. Column G does have empty cells.
3. Destination sheet has headings in the first few rows, so the data will start pasting in the first available row after the fifth row.
So far, this is what I have..
Sub test()
Dim rng As Range, c As Range, dest As Range
With Worksheets("APPLICATIONS")
Set rng = Range(.Range("H5"), .Cells(Rows.Count, "H").End(xlUp))
For Each c In rng
If c = "FUNDED" Then
c.EntireRow.Copy
With Worksheets("FUNDINGS")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
ElseIf c = "DENIED" Then
c.EntireRow.Copy
With Worksheets("DENIALS")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
End If
Next c
End With
Application.CutCopyMode = False
End Sub
This works, but I how can I cut and paste rather than copy and paste, and/or delete the original row in the worksheet?
Also, is there a way to copy a specific column range of the row, instead of the entire row?
Please help.Configuration: Windows Vista
Firefox 3.5.2
