Relative Copy Sheet, Replace All 1

[sandcounter]

I have a workbook used for job reporting each day. Each day uses a new worksheet and formulas in each sheet reference the day before for a rolling total. Each day, the user has to copy the last work sheet, and perform a "replace all" to change formula code to reference the day before instead of two days before. For example:

on "Day 3" the user copies the "Day 2" sheet and replaces all 'Day 1'! with 'Day 2'! and then name the sheet "Day 3"

I am trying to automate the process using a vba macro, but I am running into trouble making the code smart enough to replace the 'Day #'! with 'Day @'! where # = Today - 2 and @ = Today - 1.

Any help would be appriciated, See my code below:

Sub NewReport()
'Copy sheet
ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet
'Replace Cells names
Cells.Replace What:="'" & Sheet1.Name & "'!", Replacement:="'" & Sheet2.Name & "'!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Name Sheet
ActiveSheet.Name = "Day " & Sheet1.Name
End Sub

 

[SkipVought]

All that replacing is not necessary and it is not good design.

The day before today is Yesterday = Date - 1 unless you have a weekend or holiday. Check out the WEEKDAY function. I'd do it ALL on the sheet rather than in code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sandcounter]

SkipVought, thank you for replying so quickly, unfortunately I think that you misunderstand my problem. I am trying to change cell references within my formulas on a new sheet. For example, I have about 335 cells with a formula similar to:

=IF($B$11=$H$11,B16,IF($C$11=$H$11,C16,IF($D$11=$H$11,D16,0)))+'Day 1'!H16

which I would like to change to something like:

=IF($B$11=$H$11,B16,IF($C$11=$H$11,C16,IF($D$11=$H$11,D16,0)))+'Day 2'!H16

You see, it is the name of the referenced sheet that I am trying to change, not just the displayed day of the week.

I hope I have clarified where I need help.

 

[MakeItSo]

"I have a workbook used for job reporting ... Each day, the user has to copy the last work sheet, and perform a "replace all" to change formula code to reference the day before"

Jeeeeeez - thank God you are not my boss...
If I were you, I'd rather think of a different reporting method rather than trying to automate a bad one!

Just my 2$ as an overburdened employee...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[sandcounter]

"a different reporting method "

Any ideas would be helpful. I do not have the authority to change the method of reporting, and everyone is content with the overall process. Unfortunately I do not have the luxury of suggesting throwing out a proven method of reporting which has been in place for several years and has more advantages than disadvantages. MakeItSo, I am not sure why you feel qualified to make an assesment about an industry of which you likely have no knowledge. Your post was not very valuable. If I was your boss, I wouldn't be for long.

 

[MakeItSo]

Sandcounter: Having used a method for years and being accustomed to it does not necessarily make it a good one.
I have seen - and performed - more than enough "good and functioning" methods to have a certain hunch which ones will keep people longer from productive work than necessary.

I didn't "make an assesment about an industry", but a about a method, a process. Since I am a professional - also in analyzing processes and methods - I felt not only qualified, but compelled to make such an assessment.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[sandcounter]

I am only interested in solutions.

 

[MakeItSo]

Of course you are.

This would be one, for example:

http://sourceforge.net/projects/tsheet/

an OpenSource (i.e. free) timetracking app with daily/weekly timesheets, scheduler, reporting etc.

Your excel solution might indeed work fine, but can you easily USE the data in it, e.g. for statistics on workload? Timekillers? etc.?

Just a few things to consider.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

AMEN!!!

I was about to make some comments along the same line.

You seems to be storing these sheets of data, correct? BAD STORAGE DESIGN!

Data of a similar kind, like you have described here, ought to be stored in a single table. Data is a corporate asset, and storing your data, all chopped up by day in a non-tabular format, renders it about as useless as a paper report in a file drawer. It cannot be easily analysed or reported.

Spreadsheet users often mistake best and accepted table/storage design with report formats or data entry formats, which appear to make sense to the eye, but are useless when it comes to data analysis and reporting.

Your data COULD tell you what happened last week, last month or last year in an INSTANT, if your data structure were properly designed.

You're putting all this effort into saving something that is virtually useless.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[MakeItSo]

I can't let you suffer.
This code should do it:

Sub NewReport()
Dim i As Integer
'Copy sheet
    i = Sheets.Count
    Sheets(i).Copy After:=Sheets(i)
'Name Sheet
    Sheets(i + 1).Name = "Day " & i + 1
    Sheets(i + 1).Select
'Replace Cells names
    Cells.Replace What:="'" & Sheets(i - 1).Name & "'!", Replacement:="'" & Sheets(i).Name & "'!", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Explanation: Why select the ActiveSheet? If it is the active sheet, it is already active. Name says it. ;-)

This of course can be accomplished without ANY select at all, too.

However, please, please, use it only as a crutch! Information management is something vital and above all profitable!
Return on Investment requires a wee bit investment - and one in a proper resource/time management system will return quite a bit.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[sandcounter]

MakeItSo,
That was very helpful. Thank you for the time you put in to put that together. I sincerely apologize for my harsh comment toward you.
You have made a management-mandated reporting method much more palatable for myself and other field personnel and it should be able to hold us over until we can convince management to adopt something more efficient.

 

[MakeItSo]

Thanks!

I guess my comment at "you as boss" wasn't all too subtle either.
I just imagined myself copy/pasting excel sheets just to see them gather dust on an archived tape, and that sort of got me...


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell