Excel - How to create a worksheet that refresh daily and save old info

Question

Basically Im creating a simple worksheet for my mom to calculate workers' salary and manage her import and export products. I'm wondering if there is a way to make the information refresh daily so she can input new info but meanwhile still able to access the old info if she wanted to. The way Im doing it right now is making copies of the one worksheet daily and rename them with the date. But it seems unprofessional and I'd think there's a way to do this more effectively.

Solution

I think all you want to do is automate your current process as it seems that for your needs, it works fine. I will suspect you'll run into issues at later dates as the amount of data you input/store increases and that you'll need a more suited application (or use new Excel sheets if it gets too big).

Create a new spreadsheet and name it template. Ensure the format is what you want on this page. So enter the 'headings' but no values (see an example in the before image below)

Then you run this code

Option Explicit
Sub CopyIt()


Dim ws As Worksheet
Set ws = Worksheets("Template")

ws.Copy before:=Worksheets(1)

Dim d As Date

ActiveSheet.Range("A1").Value = CStr(Date)    'update this where you want the date to be
ActiveSheet.Name = Format(Date, "mm-dd-yyyy")    'update this for your chosen date format

End Sub

There is no undo with VBa so take a back up first

Before

After

How do I add VBA in MS Office?

Comments

[ejbytes]

I agree with one of the solutions below. What you want is a database, not a spreadsheet. You'd of done that if you already had experience with them, so it's obvious that you aren't familiar with them. With MS Access you create tables which are manipulated with "Queries". You can also sort the data by any date and view these tables via "Forms". The forms link data form the tables. The queries sort the data from said tables. It's a set of skills that takes time learning, but worth every minute. A few online tutorials should help you get started.