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)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,813
Messages
5,855,785
Members
431,763
Latest member
AlexisChch

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