Do I use a formula or a macro to copy cells?

[ak_edm]

(3rd repost of this question - no responses)

Column B contains a bunch of information for Monday, March 23rd. Column C
contains a bunch of information for Tuesday, March 24th and so on. The data
is set up so it’s easy to print out on a sheet, and every week the
information is overwritten with the new week’s information. Column B becomes
Monday, March 30th, column C becomes Tuesday, March 31st and so on. Every
week the dates are updated and the columns re-written.

I want to preserve the information so long-term trends etc can be analyzed.
So I’m thinking that I’ll pull the information into another sheet for each
day, and wow wouldn’t it be great if I could do this automatically. I’ll set
up another sheet where each row represents a date: row 1 will be March 23rd,
row 2 will be March 24th and so on. Then the information from Monday, March
23 (column B) can be placed into the first row, the information from Tuesday,
March 24(column C) can be placed into the second row, and so on. It would be
a real-time update so as the information for the current day changes then the
preserved information also changes, and the beauty is that once that day is
over and it’s the next day, then that preserved information from the day
before is no longer touched.

How do I do it? LOL.

You’d think the formula “=B1†would do what I need, but B1 will eventually
represent the next week. I don’t want to overwrite my preserved information
from the week before so I need a condition – an IF function based on date.
But an IF function requires a false action : IF true then copy, IF false
then…two quotes? A zero? Hmmm. No matter what I place into the function, the
false condition blanks my data. I cant find a way to leave the data alone.
I cannot preserve it.

The problem with using a formula to grab data from a fixed location such as
the numbers in a cell black is that the source numbers will eventually
change, and since the formula to grab that information will always remain
active, it too will eventually overwrite the original data I wanted to
preserve, which is what I don't want, or it's going to return a "" or zero
value because I tried to make it conditional on date March 23 only, which is
also what I don't want.

I need a "do nothing" so March 23 is preserved once it's written into the
row that preserves information for March 23 and it’s no longer March 23rd.
I'm thinking I need a macro, VBA, something.

I need a macro or VBA code to say :

IF today is March 23, 2009 then write to here, else leave this location
alone (don't write "" double quotes, don't write a zero, dont erase the
information you wrote earlier, just leave this location alone and move on,
whatever number that was brought to this location on March 23, 2009
just leave it alone, it must not be March 23, 2009 anymore so move on).

The next row, because it's a new day, will be similar : IF today is March
24, 2009 then write to here, else...yadda yadda yadda

And so on for the week of March 23, 2009.

Next week column B now represents March 30, 2009, and there's a new row for
preserving the information: IF today is March 30, 2009 then write to here,
else...yadda yadda yadda


Preservation Sheet:
Row 1 : March 23 – the information for March 23 goes here.
Row 2 : March 24 – the information for March 24 goes here.
…
Row 8 : March 30 – the information for March 30 goes here.
…

- Eric

 

[Per Jessen]

Hi Eric

I think you are complicating things.

Why not transfer data to Preservation Sheet once a day or maybe once a week.

Copy the column(s) from input sheet and transpose to first empty row in
Preservation Sheet.

If the workbook is saved once a day we could set up a macro to transfer
todays data to Preservation Sheet before the workbook is saved.

Let me know what you think.

 

[ak_edm]

That sounds good. I'll be saving the sheet at least once a day, likely more.
I do want to transfer the data to a preservation sheet too. I'd want the
information though to overwrite the same day's information if the data is
saved twice or more often a day. In other words I wouldnt want 2, 3, 4, 5
copies of the same day's information - just one is fine as long as it's the
latest information for that day. Then the next day would be a new matter.

- Eric

 

[Per Jessen]

Hi Eric

This is an event code so it has to be pasted into the codesheet for
ThisWorkbook. Change sheet names in the code to actual sheet names.

The macro will fire when you save the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Set InputSh = Worksheets("InputSheet") 'Change to suit
Set PreSh = Worksheets("PreservationSheet") 'Change to suit
TargetDay = Format(Date, "dddd")
Set TransposeTo = PreSh.Range("A" & Rows.Count).End(xlUp)
If TransposeTo.Value <> TargetDay Then
Set TransposeTo = TransposeTffset(1, 0)
End If
Select Case TargetDay
Case Is = "monday"
TargetCol = "B"
Case Is = "tuesday"
TargetCol = " C"
Case Is = "wedensday"
TargetCol = "D"
Case Is = "thursday"
TargetCol = "E"
Case Is = "friday"
TargetCol = "F"
Case Is = "saterday"
TargetCol = "G"
Case Is = "sunday"
TargetCol = "H"
End Select

Range(TargetCol & 1, Range(TargetCol & Rows.Count).End(xlUp)).Copy
TransposeTo.PasteSpecial Transpose:=True
End Sub

Regards,
Per