How do I create a command button that will run a macro as fo

March 6, 2017 at 02:41:58
Specs: Windows 8
Hi everyone,

I have an excel workbook with 7 sheets. ("Info","User1","User2","User3","User4","Completed" and "Booked")

The data list starts from row 5 (above it is the header area)

Starting from column A5, I have a drop down list(open/Completed/Booked).

I am looking for a a macro which cuts the entire row and paste it to the next empty row in my other worksheet "Completed", once i select the "Completed" drop down.

In the same time it should delete the empty row in my Initial sheet "Info"

This macro will be used on various sheets to cut and paste the data to the "Completed" sheet.

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

message edited by JohanW


See More: How do I create a command button that will run a macro as fo

Report •

#1
March 6, 2017 at 03:32:32
You could use a Worksheet_Change macro.

http://www.ozgrid.com/VBA/run-macro...

I can't tell if you need help with the code itself or just getting it to run when you want it to. If you already have some code written and you need help with it, please post what you have, after reading the instructions found at the following link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 7, 2017 at 03:37:20
Hi I have no code. I need help with this.

Report •

#3
March 7, 2017 at 07:40:40
I'm a little confused by these statements:

In the same time it should delete the empty row in my Initial sheet "Info"

and

This macro will be used on various sheets to cut and paste the data to the "Completed" sheet.

If the macro is used in "various sheets" e.g. User1, User2, etc. does it still need to delete rows in the "Info" sheet each time it is run in one of the other sheets? If so, how will it know what Row to delete in the Info sheet? Is there something unique that the code can search for so that it finds the exact Info row to delete?

If, on the other hand, you mean that the code needs to delete the row only from the specific sheet where Completed was selected, then the code posted below should work.

The code posted below needs to be placed into the sheet module for every sheet in which you want the cut/delete to occur. This can be done by right-clicking the sheet tab for each sheet and choosing "View Code". The code can then be pasted directly into the pane that opens. You should not place the code in the Completed sheet module.

The code will then fire when any change is made to a sheet containing the code. If the change is made in Column A and the changed cell reads "Completed", the row will "moved" to the Completed sheet.

You should be aware of the following instructions:

     Application.EnableEvents = False

and

     Application.EnableEvents = True

What these lines do is Disable/Enable the Change event in the workbook. The reason for them is to prevent the code from firing again when the Target Row is deleted. Since the deletion is a "change", the code will want to refire and that is sloppy/inefficient.

The reason I point that out is as follows:

If you are playing around with the code and cause it to error out after Events are disabled (= False) but before they are Enabled (= True) the code will not fire again. That can be very confusing because there is no indication that Events are disabled. Basically, nothing will happen when you make a change because Excel will not be monitoring Events. An easy fix to that is to run a quick macro in any module to re-enable Events:

Sub FixEvents()
 Application.EnableEvents = True
End Sub

Ok, with that warning in mind, here is some code that should work for you. Let me know if it fits your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine last Row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Determine if Completed was selected in Column A
   If Not Intersect(Target, Range("A5, A" & lastRw)) Is Nothing Then
    If Target = "Completed" Then
'If Yes, then Disable Events
     Application.EnableEvents = False
'Determine next empty row on Completed sheet
      nxtRw = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy/Paste/Delete
        Target.EntireRow.Copy Sheets("Completed").Range("A" & nxtRw)
        Target.EntireRow.Delete shift:=xlUp
'Renable Events
     Application.EnableEvents = True
    End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question