-1

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.

Phuong Nguyen
  • 3
  • 1
  • 1
  • 2
  • 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. – ejbytes Aug 01 '16 at 23:43

2 Answers2

1

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

enter image description here

After

enter image description here

How do I add VBA in MS Office?

Community
  • 1
Dave
  • 25,297
  • 10
  • 57
  • 69
0

Spreadsheets by there very nature are static. They are very reminiscent of writings things into a physical orders book/ payment journal.

As Dave suggests, you could write a VBA function that at a click of a button Saves the worksheet, but then that is just an automated version of what your doing now and is not neat.

If you wanted to stick with an excel sheet, you could change it from daily to monthly. That would be more inline with what businesses do. The sheet would be updated daily, but a blank copy created each month. This gives you easy to access archival and you can see what is happening in your current month.

Realistically an Access database would probably be more suitable. You can have a table for imports and or exports. A table for Staff, and a separate table for their pay which is then linked.

These can then be brought together using forms to make it user friendly. You can even have the access database link to a read only spreadsheet that is used to input data so that there isn't such a large learning curve.

The proper design of such a database goes beyond an answer on here, there is much to learn on how to do it properly and maintain it both for user sanity and your own.

Lister
  • 1,305
  • 8
  • 20