Macro to create new worksheet from the current worksheet, with additional changes

[sards08]

Hi,

I have a workbook that is updated manually daily, and I was hoping that a macro could be created to save some time!! The workbook is a running list of current jobs in a workshop, with each day separated into a different worksheet. Upon pressing the macro button ("New Worksheet" as an example) I would like the following to occur:

1. A new work sheet is created and renamed in the ddmmyy format
2. Data from the previous day (previous worksheet) is copied into that new worksheet
3. If the item was despatched (="YES") on the previous day it is not copied into the new worksheet
4. The index/match formulas for the workbook are updated so that they always reference the new worksheet and the previous worksheet

Here is a copy of the workbook Jobs.xlsm

Hopefully someone can help with this --- Thankyou
Steve

[LJMetzger]

Steve,

Your question was well written, and you have an excellent spreadsheet design.

Try the attached worksheet (written and tested in Excel 2003). It should work
in your version. I think it does all the things you ask plus:

a. I changed the 'ReOrder' macro to automatically work for the 'Active' Worksheet.

b. It has a button that will create a backup file for you.

c. It keeps the formulas in column 'N' for the last 3 working days only.
For the other days, the formulas in column 'N' are converted to numbers.
It scares me when there are a lot of forward references. It also allows you
to remove days no longer in use.

d. I set the 'ZOOM' to 88% so all the data could be displayed on my screen.

e. The Workbook is automatically saved after the 'new day' is created.


Please double check to make sure the following items work properly before you
use it in real life:
a. Despatched items
b. Calculation of hours in column N.

Lewis

[sards08]

Hi Lewis,

Thanks for that. I have had a play with the workbook and it seems to work well. Obviously I can only open one new sheet each day, as it is based on the date, but from what I can see it does everything that I need. Over the next week I'll have a better indication, but I really appreciate your help.

Thanks mate,
Steve

[sards08]

I have been entering some test data into the workbook, periodically over the last two weeks, and it appears to always be referencing worksheet 281013 in the "match" formula in column N (running hours tally). Is it possible for the formula to reference the previous worksheet each time (in the case of 131113 we would reference 121113 for the match, whereas 11113 would reference 061113 for that match)?

Jobs List.xls

Steve

[LJMetzger]

Steve,

I apologize for the error in the previous file I uploaded, caused by poor testing on my part. Try this file which should correct the problem. Be careful when cutting and pasting Sheets between Workbooks. Sometimes the BUTTONS reference an OLD FILE instead of the current file.

Please let me know if there are any other issues or the same issue.

Lewis