Paste cell range depending on cell value

samchox

New Member
Joined
Feb 2, 2004
Messages
29
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.

(y)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top