Hello Community,
I am looking for creating a Button which will move entire row from a table to a new/existing tab (if run first time than New and if clicking second or subsequent times, existing tab after the last row which is non-blank)
Refer Below The image:
I want to move completed and deferred based on column F i.e. "Project Commitment Filler (Hide) column.
Also if there are time stamp after each row of completed and deferred in the new tab/existing tab it would be helpful.
Thanks for your help in advance.
dhavalpaun,
If I understand correctly, you want to create two new worksheets to move "Deferred" and "Completed" data into.
Since there are only two sheets that you will be copying data to, why not just create/name both to begin with and be done with that.
The following code will move the "Deferred" and "Completed" items per column F to their respectively named sheets with a time stamp in column I.
On Sheet1 place an ActiveX Command Button or a Form Control Button. Both buttons are available on the 'Developer' tab.
See below "To add an ActiveX Command Button".
Try this code on a copy of your worksheet so you don't lose any data.
Perpa
Code:
Sub Test()
Move "Deferred" and "Completed" items with time stamp
Dim RowNum As Long
Dim LastRow1, LastRow2, LastRow3 As Long
'Assumes you have the raw data sheet (Sheet1) open
LastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For RowNum = 5 To LastRow1
If Cells(RowNum, "F") = "Completed" Then
LastRow2 = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Rows(RowNum).Copy
Sheets("Completed").Cells(LastRow2, "A").PasteSpecial
Sheets("Completed").Cells(LastRow2, "I") = Format(Now, "hh:mm") 'Puts time stamp in column I of copied row
End If
If Cells(RowNum, "F") = "Deferred" Then
LastRow3 = Sheets("Deferred").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Rows(RowNum).Copy
Sheets("Deferred").Cells(LastRow3, "A").PasteSpecial
Sheets("Deferred").Cells(LastRow3, "I") = Format(Now, "hh:mm") 'Puts time stamp in column I of copied row
End If
Next
For RowNum = LastRow1 To 5 Step -1 'Deletes copied rows from Last to row 5 from Sheet1
If Cells(RowNum, "F") = "Deferred" Or Cells(RowNum, "F") = "Completed" Then Rows(RowNum).Delete
Next
'Puts the curson on all sheets in cell A1
Sheets("Completed").Select
Range("A1").Select
Sheets("Deferred").Select
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
To add an ActiveX Command Button:
1 - You need to show the Developer Tab in the ribbon at the top of the sheet... Click on the Windows icon, the
4 colored boxes in the circle at the top lefthand of the screen, next to 'Home' (Excel 2007, may be different in later versions).
At the bottom of that tab on 'Excel Options', click on the 'Popular', then 'Show Developer tab in the Ribbon'. Then click 'OK' at the bottom of that tab. You will notice that the 'Developer' tab has been added to the ribbon. Now save your workbook.
2 - Select the sheet where the button will go. Left click on the Developer tab. In the middle of that tab is an
icon labled 'Insert'. Click on that icon and you will see 2 sets of controls, you want the ActiveX group, hover
the cursor over the icon that looks like a rectangular button. It should say 'Command Button - (ActiveX Control)'.
Click on that icon, then down where you want the top LH corner to be, left click, hold and drag down to
where you want the lower RH corner. You should have created a button labled 'CommandButton1'.
It doesn't do anything (yet) but it looks nice.
3 - To get the button to work, you need to tell it what code to run. Put the cursor over the button, then
right click. Select 'View Code' and you will enter the VB Editor where the main screen shows these two lines of code:
Code:
'Private Sub CommandButton1_Click()'
'End Sub'
The code will go between those lines...copy and paste the code between the above lines, BUT make sure not
to copy the first and last lines of that code, ie. 'Sub Test()' and 'End Sub'. The above two lines
replace those. Close the VB Editor.
Select the Developer tab and notice that the icon 'Design Mode' has been highlighted. Click on that that icon,
to deselect Design Mode, then Save the spreadsheet. Your Command Button is ready to use! You may need to
'enable macros' when you open this sheet again depending on the version of Excel you have.
4 - You can modify the Command Button by clicking the Developer tab, then clicking 'Design Mode', then right
click the Command Button1.
To change the TEXT in the button - Select 'Command Button Object' then 'Edit' - click on the text and
enter something like 'Add Picture'.
To change the SIZE of the command button - click on the button, the button should be outlined with
bubbles, click and hold in any corner/side where the bubbles are, then drag to enlarge or make smaller.
To change the COLOR of the command button - Right click the command button, select 'Properties' to
see a list of Properties...select the grey square opposite 'BackColor'. Select the down arrow to get a
palette of possible colors. Use the slide selector to see all the colors possible. After selecting a color
close that window.
To adjust the LOCATION just click and drag where you want it.
When you are done modifying the command button, you need to close the Design Mode in the
Developer tab (just click it), then Save the spreadsheet to save your changes.
