Archive Rows Code Help

rfarnham

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
Hello!

I am updating a document for work and which shows projects being worked on and their status.

I would like rows which have been marked as "Archive" to be automatically moved onto another worksheet so as not to clog up the current work. How do I input a code to enable that to work properly? I'm pretty new to this and would love some help. I attached a screenshot to show the document.

Working Sheet would be "Project Status 2.0" and the rows to be moved to ARCHIVE



Thank you!
 

Attachments

  • Screen Shot 2021-04-01 at 4.49.27 PM.jpg
    Screen Shot 2021-04-01 at 4.49.27 PM.jpg
    70.1 KB · Views: 28

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hello rfarnham,

In which column is the criteria "Archive" to be entered?

Cheerio,
vcoolio.
 
Upvote 0
Apologies here you can see more clearly. I created a drop down menu so that when a project is complete the person working on it can select Archive.
 

Attachments

  • Screen Shot 2021-04-01 at 6.35.55 PM.jpg
    Screen Shot 2021-04-01 at 6.35.55 PM.jpg
    123.3 KB · Views: 31
Upvote 0
Hello rfarnham,

The following code may help:-

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsA As Worksheet: Set wsA = Sheets("ARCHIVES")

        If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub
        
Application.ScreenUpdating = False

        If Target.Value = "Archive" Then
              Target.EntireRow.Copy wsA.Range("A" & Rows.Count).End(3)(2)
              Target.EntireRow.Delete
        End If

Application.ScreenUpdating = True

End Sub

The code is a Worksheet_Change event code. Once you select "Archive" from a drop down in Column B, the code will transfer the relevant row of data to the Archive sheet and then delete the row from the Project Status 2.0 sheet.

To implement the code:-

- Right click on the Project Status 2.0 sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
This is amazing thank you! Is there a way of if on Archives a project status is change back to say Active it would revert back to the original sheet? Thank you so so much!
 
Upvote 0
Hello rfarnham,

You could use the same code but place it in the "ARCHIVES" sheet module instead (same instructions to implement the code as in post #4) but change this line:

VBA Code:
Dim wsA As Worksheet: Set wsA = Sheets("ARCHIVES")

to

VBA Code:
Dim wsPS As Worksheet: Set wsPS = Sheets("PROJECT STATUS 2.0")

and these two lines:

VBA Code:
 If Target.Value = "Archive" Then
              Target.EntireRow.Copy wsA.Range("A" & Rows.Count).End(3)(2)

to

VBA Code:
If Target.Value = "Active" Then
              Target.EntireRow.Copy wsPS.Range("A" & Rows.Count).End(3)(2)

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
I just tried shifting this into my Sharepoint version but the code doesnt work - do you have any advice?
 
Upvote 0
Hello rfarnham,

I personally have never been bothered with Sharepoint but the general consensus is that it doesn't support macros.
If you subscribe to Microsoft 365, and Sharepoint is in your suite, then it would be a cloud based service and, as with pretty much all cloud-based services, macros are not supported and files need to be downloaded to a local desktop in order for macros to work.

As this is a different query to your original post, please start a new thread explaining the problems you are having. Someone more familiar with Sharepoint may then be able to give you some guidance.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top