# creating an archive in VBA



## newtoVBA502 (Dec 21, 2022)

hello, I'm new to VBA. I would like to create a macro in VBA where it pulls data from a sheet and create a copy of the value in a single column with the date on top. I'm trying to create an archive of each time I use the sheet to calculate a different thing. thank you for your help.


----------



## bferraz (Dec 28, 2022)

Can you provide more information? Sharing your workbook or providing which cell is the result of your calculation.


----------



## bferraz (Dec 28, 2022)

Try using the following code. Note that is a Sheet Module, so you have to paste it directly on the code section of the sheet where the calculation happens.


```
Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim lastrow As Long
    Set rng = Range("C2") 'Change this to the cell where is the result of your calculation
    If Not Intersect(rng, rng) Is Nothing Then
    
    
    
        With ThisWorkbook.Sheets("Archive")
    
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            
        .Range("A" & lastrow) = Now
        .Range("B" & lastrow) = rng.Value
        
        End With
        
    End If
End Sub
```

Remember that for this code to work, you will need to change the rng variable for the range which stores the value you want on the Archive.
Also, create a new sheet and name it as "Archive". The headers for this sheet are "Time" and "Value", which means the date and time are stored on column "A" and the value calculated on column "B". 

Let me know if it works or you need further help.


----------

