MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sending totals to another sheet


Posted by Bazza on November 12, 2001 11:12 AM

i would like to take values of some cells in one sheet (called daily) and enter the values against todays date in another sheet (called monthly), any help please?


Posted by Damon Ostrander on November 12, 2001 2:04 PM

Hi Bazza,

In order to transfer data in VBA, one only needs to set the value of the desired cell (or range) on a sheet to the value of the desired cell (or range) on the other sheet, being sure to qualify (i.e., define the worksheet for) the ranges of interest. For example, to put the value of cell B3 in Daily to cell C5 in Monthly:

Sheets("Monthly").[C5] = Sheets("Daily").[B3]

If you want to transfer a range of cells from one sheet to another, you must put the values into a VBA array, so the code would look like this:

Dim Temp As Variant
Temp = Sheets("Daily").[B3:B18]
Sheets("Monthly").[C5:C20] = Temp

In this case, the variant variable Temp is not itself an array, but contains an array.

Happy transferring.

Damon

Posted by Bazza on November 12, 2001 2:15 PM


thanks Damon, i was hoping for it to look for "that days date" on the monthly sheet from dates in a range of cells on that sheet

Posted by Damon Ostrander on November 14, 2001 9:51 AM

Hi again Bazza,

To give you a specific solution, some more information is needed. When you say "against" today's date, what does that mean? Put it in the cell just to the right of any cell containing the date? And when you refer to today's date on the destination sheet, is it just that (a date), or is it a full date-time value such as would be yielded by the NOW() function? Are there multiple cells on the destination sheet containing the same date?

Damon