Paste cell range depending on cell value

[samchox]

I have a summary sheet with range A2:D40 where data values are collected everyday. At the end of the day I'd like that entire block of datda to be pasted in a specific sheet ina specific range depending on a particular cell value.

For eg.
If A1 is 2-Feb, range A2:D40 needs to be pasted in sheet "Feb" in range A42:D80.

If A1 is 3-Feb, range A2:D40 needs to be pasted in sheet "Feb" in range A82:D120

I can setup an ontime macro to perform this task at say 23:30 at End of Business, however, it needs to select the target range depending on cell A1 in summary sheet.

Thanks in advance for your help.

 

[Damon Ostrander]

Hi samchox,

Unless there is some rule (e.g., each day's data down 40 rows) for determining where a particular date's data should be pasted so that a more compact algorithmic approach could be used your best bet is probably the Select Case. It would look like this:

Select Case [A1].Value
Case #2/2/03#: [A2:D40].Copy Destination:=[Feb!A42:D80]
Case #2/3/03#: [A2:D40].Copy Destination:=[Feb!A82:D120]
.
.
.
End Select

Also, do you want it to pick up the month sheet tabname from the date value in A1? If so, this could be easily arranged.

Damon