Macro to track data

  • Thread starter Thread starter Oreg
  • Start date Start date
O

Oreg

I am trying to create a macro that will automatically track data tha
changes daily in cells B1:K1. Starting in row 5, I would like the
column to display the current date and B5:K5 would display that dat
shown in B1:K1 at time the spreadsheet is opened.
Everytime the spreadsheet is opened the next row below would displa
the new data. Is this possible??

Thanks,


Ore
 
I am a beginner at programming macros, so you need to take
this suggestion in that light.

1. Save and close all open workbooks.
2. Open a new workbook, and then start the Visual Basic
Editor (press ALT+F11).
3. Press CTRL+R to switch to the Project Explorer window.
4. In the Project Explorer window, right-click the
ThisWorkbook object, and click View Code on the shortcut
menu.
5. In the module, type the following code:

Private Sub Workbook_Open()
Dim NumRows As Double
NumRows = Range("A65536").End(xlUp).Row
If Range("B1").Value = "" Then
GoTo quit
ElseIf NumRows = 1 Then
Range("A5") = Now()
Range("B5") = Cells(1, 2).Value
Range("C5") = Cells(1, 3).Value
Range("D5") = Cells(1, 4).Value
Range("E5") = Cells(1, 5).Value
Range("F5") = Cells(1, 6).Value
Range("G5") = Cells(1, 7).Value
Range("H5") = Cells(1, 8).Value
Range("I5") = Cells(1, 9).Value
Range("J5") = Cells(1, 10).Value
Range("K5") = Cells(1, 11).Value
Else
Range("A" & NumRows + 1) = Now()
Range("B" & NumRows + 1) = Cells(1, 2).Value
Range("C" & NumRows + 1) = Cells(1, 3).Value
Range("D" & NumRows + 1) = Cells(1, 4).Value
Range("E" & NumRows + 1) = Cells(1, 5).Value
Range("F" & NumRows + 1) = Cells(1, 6).Value
Range("G" & NumRows + 1) = Cells(1, 7).Value
Range("H" & NumRows + 1) = Cells(1, 8).Value
Range("I" & NumRows + 1) = Cells(1, 9).Value
Range("J" & NumRows + 1) = Cells(1, 10).Value
Range("K" & NumRows + 1) = Cells(1, 11).Value
End If

quit:
End Sub

Switch to Microsoft Excel and save the workbook.
Close and reopen the workbook.
 
Back
Top