How do I create an incremental document dump/backup?

[GingerBeardo]

I have a document that is intended to be a living document and contain a large volume of data entries. For the sake of stability and protection I want to create VBA script to look at each entry. If the values in column "D" for said entries are all value "X" they are to be copied to a separate backup log then removed from the current document. This way once the data isn't actively needed, once its status has changed to value "X" it is archived in the backup log.

 

[Logit]

.

Option Explicit


Sub CopyOwnTab()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
On Error GoTo M
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
    For i = 2 To Lastrow
    ans = Sheets("Master").Cells(i, 1).Value
        Sheets("Master").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No such sheet as  " & ans & " exist"
Application.ScreenUpdating = True
End Sub

Download sample workbook : https://www.amazon.com/clouddrive/share/S3vsqVQYa0WsRdxjd9WWPPcKvX7QkpchGt5qW26wHuz


The macro is set for Col A. It can be edited to suit.

 

[GingerBeardo]

Forgive me if I dont understand and if I explained poorly but what I am seeing in the sample document takes the entries from the first sheet column A and sorts them into new sheets by name in the same document while the original information remains on the first sheet.

What I am asking help with is, in my document when asset "B" is checked out its status is "OUT" and when checked in its status is "IN". Once the status has been changed to "IN" I have no need to keep it in the originating document where it is actively tracked. So, I want to copy each entry when its status has changed from "OUT" to "IN" to a 2nd workbook (the back up log) for archiving and remove the listing from the original workbook. This way the original document does not become over burdened with a mass amount of data in a short period and negatively impacting the tracking performance due to the sheer volume it will incur.

 

[GingerBeardo]

Here are two documents. The SmartTracker is the original document, AssetBKUP is the location I am wanting to back up to.

Sample workbooks: https://www.amazon.com/clouddrive/share/PXN8mfkAGCG4OejtxAiPjcy1YHg7hF85qFWPhok6Ym1

 

[Logit]

.
Using the supplied macro you can expand on that to move the data to another document. It will require additional coding.

I can assist but at the moment will be going out for a few hours. Will take a look at your files upon my return.

Thanks.

 

[Logit]

.
Replace the existing macro with this one :

Private Sub CheckIn_Click()


Dim FoundRange As Range
Dim Status As Range
    
Set FoundRange = Columns("C").Find(What:=TextBox2.Text, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
Application.ScreenUpdating = False


    If Not FoundRange Is Nothing Then
        Set Status = FoundRange.Offset(ColumnOffset:=1)
        Status.Value = "IN"
       
        FoundRange.EntireRow.Select
        Selection.Copy
        
        Workbooks.Open (Environ("UserProfile") & "\Desktop\AssetBKUP.xlsx")
        Worksheets("Sheet1").Select
        
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
         
        ActiveSheet.Cells(erow, 1).Select
        ActiveSheet.Paste
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        FoundRange.EntireRow.Delete
        Application.CutCopyMode = False
               
       TextBox2 = ""
       ThisWorkbook.Save
    Else
        TextBox2 = ""
        TextBox1.SetFocus
        MsgBox "Not Found"
    End If
    
Application.ScreenUpdating = True


End Sub

The macro is coded for workbook ASSETBKUP.XLSX to be located on the desktop. If you desire a different location, edit this line :

Workbooks.Open (Environ("UserProfile") & "\Desktop\AssetBKUP.xlsx")

the meet the new requirement.

 

[GingerBeardo]

Fantastic job, Thank you. I have a question how do I tweak it to copy only column A:D of whatever row the status change occurs?