Macro Magic [Closed]

Report
Posts
1
Registration date
Sunday August 23, 2009
Status
Member
Last seen
September 7, 2009
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
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.

3 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Dear Enisea

Now with your code you have copied and pasted
Add the same code with changes for deletion at the end of the procedure (include c.EntireRow.delete instead of copy, exclude all pastespecial statements)

Try deleting from last row to first row if you face any difficulty
I almost have all this figured out... I appreciate your help.

Is this what your talking about?

Sub MoveMerchant()
Dim rng As Range, c As Range, dest As Range
With Worksheets("SUBMISSIONS")
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.Paste
c.EntireRow.Delete
End With
ElseIf c = "DENIED" Then
c.EntireRow.Copy
With Worksheets("DENIALS")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.Paste
c.EntireRow.Delete
End With
End If
Next c
End With
Application.CutCopyMode = False
End Sub

This is not working.
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Use Range("SomeSource").Cut Destination:=Range("SomeDestn")
Use Range("SomeSource").Cut Destination:=Range("SomeDestn")

Would this be referring to only copying a specific column range?

i.e.

Use Range("A").Cut Destination:=Range("X")

OR

Use Range("A:A").Cut Destination:=Range("X:X")
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
try yourself
Columns("B:B").Cut Destination:=Columns("D:D")

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!