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

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

On Mar 28, 6:41*am, john.9.willi...@bt.com wrote:
> 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

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

--
Dan

On Mar 28, 6:41*am, john.9.willi...@bt.com wrote:
> 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

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


<(E-Mail Removed)> wrote in message
news:3940338f-88b7-4629-8092-(E-Mail Removed)...
>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