Hello, everybody here.

I'm trying to move data from sheet 1("Data Central") to sheet
2("Archives") daily. Sheet 1 is a master sheet with =TODAY() in cell
C2, formulated dates in range C5:C29, blank space for hard data in
D5:D29 and K5:K29, and money calculating formulas everywhere else.

When the month in C2 changes, dates in C5:C29 change with the formula,
D5:D29 and K5:K29 are cleared out, and the money calculating formulas
display their starting values everywhere else.

I simply want to copy, from "Data Central," the date in C5 and the
corresponding hard data in D5 and K5 to "Archive," cells A5,B5 and C5.
The next day, "Data Central" cells C6, D6 and K6 should copy to
"Archive" cells A6, B6 and C6.

The following module will do this perfect-ly, IF both sourceRange and
destRange are unformatted.

Sub copy_1()
Dim sourceRange As Range
Dim destRange As Range
Dim Yo As Integer
Yo = 25 * (Month(Sheets("Data Central").Range("C2")) - 1) + 5
Set sourceRange = Sheets("Data
Central").Range("C5:C29,D5:C29,K5:K29")
Set destrange = Sheets("Sheet2").Cells(Yo, 1)
sourceRange.Copy destrange
End Sub

However, the real master sheet has conditional formatting in Column C
and K that dark out the days of that month we don't work (always
changing).

When I applied this Macro to my previously existing master sheet, it
froze it, shooting me back to the VBA screen with a message box saying
there was a Runtime Error '9' Subscript out of Range error. What is
this about?

How can this be gotten around? Basically, how do I tweak the macro to
copy only values of these cells and not formulas or formatting?

Thanks to anyone who tackles this issue.

James