adding daily totals to weekly totals

[john.9.williams]

I have a spreadsheet called daily data it has two columns of data, at
the end of the day I want to copy the values in column b and paste
them to the spreadsheet weekly data. Each column in weekly data has
values grouped in week ending columns i.e

daily data

DATE = 28/03/08

DATA1 10
DATA2 20
DATA3 20

WEEKLY DATA

WK ENDING 28/03/07 WK ENDING 05/04/2008
DATA1 30
60
DATA2 45
60
DATA3 35
60

I need some code that can work out which week ending column to add the
daily totals too, any help greatly recieved

John

 

[Dan R.]

Assuming all of your headers are in the first row...

Sub dailyTotals()
Dim dtRng As Range, lCol As Long, lRow As Long
Dim rng As Range, found As Range
With Sheets("WEEKLY DATA")
lCol = .Range("IV1").End(xlToLeft).Column
Set dtRng = .Range(.Cells(1, 1), .Cells(1, lCol))
End With
With Sheets("daily data")
lRow = .Range("B65536").End(xlUp).Row
Set rng = .Range(.Cells(2, 2), .Cells(lRow, 2))
End With
Set found = dtRng.Find( _
Right(Sheets("daily data").Range("B1"), 8), _
LookIn:=xlValues)
If Not found Is Nothing Then
rng.Copy found.Offset(1, 0)
Else
MsgBox "Date wasn't found."
End If
End Sub

 

[Dan R.]

Use this to add a new week (assuming your headers are in the first
row):

dtString = "WK ENDING " & Format( _
Int((Now - (Now - 2) Mod 7) + 4), "dd/mm/yy")
With Sheets("WEEKLY DATA")
lCol = .Range("IV1").End(xlToLeft).Column
.Cells(1, lCol + 1) = dtString
End With

 

[Bernie Deitrick]

John,

Use a database and a pivot table based on the database.

Set up your database with (At least) three columns

Date DataSet Value

Then use the pivot table with Date as the column field, set to group by days, 7.... and drag
DataSet to the Row Field, and Value to the data field, set to sum.

HTH,
Bernie
MS Excel MVP