Archive Rows Code Help

[rfarnham]

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!

 

[vcoolio]

Hello rfarnham,

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

Cheerio,
vcoolio.

 

[rfarnham]

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.

 

[vcoolio]

Hello rfarnham,

The following code may help:-

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.

 

[rfarnham]

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!

 

[vcoolio]

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:

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

to

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

and these two lines:

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

to

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

I hope that this helps.

Cheerio,
vcoolio.

 

[rfarnham]

I just tried shifting this into my Sharepoint version but the code doesnt work - do you have any advice?

 

[vcoolio]

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.