Macro to cut/paste row into 2nd sheet

November 28, 2010 at 07:42:35
Specs: Windows Vista
Hi Experts,

I have an excel sheet with 2 workbooks. Its basically an action matrix. The actions list starts from row 12 (above is the header)and has 14 columns.

Starting from column A14 i have a drop down list(open/closed).

I would love to have a macro which cuts the entire row and paste it to the next empty row in my 2nd worksheet "closed", once i select the "closed" drop down.

In the same time it should delete the empty row in my 1st sheet "open"

I've been trying to do that with an command button but miserbly failed.

Am i dreaming or is there any one around who could help?????


See More: Macro to cut/paste row into 2nd sheet

Report •

#1
November 28, 2010 at 07:59:53
re: "I've been trying to do that with an command button but miserbly failed."

What have you tried? Maybe it just needs a little tweaking.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 28, 2010 at 08:04:43
Thats what i used for the button:

Private Sub CommandButton1_Click()
    fromrow = ActiveCell.Row
    Sheets("Closed").Select
    activerow = 1
    Range("A" & activerow).Select
    Do
    activerow = activerow + 1
    Range("A" & activerow).Select
    Loop Until Worksheets("Closed").Cells(1, activerow).Value = 0
    Sheets("Open").Select
    Rows(fromrow).Select
    Selection.Cut
    Sheets("Closed").Select
    Range("A" & activerow).Select
    Sheets("closed").Paste
End Sub


And thats for the move

Sub move()
'
' move Macro
'
' Keyboard Shortcut: Ctrl+m
'
    fromrow = ActiveCell.Row
    Sheets("Closed").Select
    torow = 12
    Range("A" & torow).Select
    
    Do
    torow = torow + 1
'   Range("A" & torow).Select
    Worksheets("Closed").Cells(torow, 1).Activate
    Loop Until Worksheets("Closed").Cells(torow, 1).Value = ""
    
    Sheets("Open").Select
    Rows(fromrow).Select
    Selection.Cut
    
    Sheets("Closed").Select
    Worksheets("Closed").Cells(torow, 1).Activate
'   Range("A" & torow).Select
    Sheets("closed").Paste
End Sub

My probblem is that the row remains in the first sheet. I dont get it automaticaly deleted and the rest of the rows shifted up

Any idea?


Report •

#3
November 28, 2010 at 08:07:12
Besides command button was only an option....i would rather have the drop down
:-)))))))

Report •

Related Solutions

#4
November 28, 2010 at 08:38:14
That sure seems like the long way around to get to your destination.

Why not use the WorksheetChange event to trigger your code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable events so code doesn't re-fire when row is deleted
 Application.EnableEvents = False
'Was the change made to Column A?
  If Target.Column = 1 Then
'If yes, does the Target read "Closed"?
   If Target = "Closed" Then
'If yes, determine next empty row in Sheet "Closed"
     nxtRw = Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy the row
      Target.EntireRow.Copy Destination:=Sheets("Closed").Range("A" & nxtRw)
'Delete the row
      Target.EntireRow.Delete shift:=xlUp
   End If
  End If
'Re-enable Events
 Application.EnableEvents = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#5
November 28, 2010 at 08:49:35
You are an absolute genius !!!!!

Or i'm a complete idiot !!!

No need to comment :-)

Thanks a million


Report •

#6
December 2, 2010 at 16:06:55
Hi there, I have NEVER done VBA editing but I need to figure this out. Derby Dad, I copied your code and pasted it into the VB editor window (right-click on sheet tab and select view code - paste into window) and saved the change (I had to change file type to xlsm).
Nothing Happens! What do I have to do to get something to occur. I have changed my sheet so that Column A (1) contains an entry that is "Open", "Closed" or "Pending" and renamed sheet 1 to "Open" sheet 2 to "Closed" and sheet 3 to "Pending". As per the previous parts of this post, I just want any row marked "Closed" to be cut and pasted into the next available row in the sheet named "Closed".

I made no changes to your code but do not know what to do to make this work; any suggestions? Isn't it just suppoed to "happen" after the code is in, if I enter "Closed" into a cell in column A?


Report •

#7
December 2, 2010 at 18:14:51
Works fine for me.

You said: "right-click on sheet tab and select view code - paste into window "

Which sheet tab do you click?

The code must go into module for the sheet in which you are making the change.

If you are entering Closed (case sensitive, no quotes) into the sheet named Open, then you need to right click the Open tab and paste it in.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 7, 2010 at 13:13:24
Thanks, it does seem to work. Must have PEBKAC-ed it!

Report •

Ask Question