Hello There,

Im working on a macro thats organizes my download bank-statements into
a per-week view. The goal being that i have an easy overview which
weeks where expensive, and which werent (and weeks which weeks are
going to be expensive..)
I got as far as sorting the data in columns, with dates for each
transaction. From the dates i calculate weeks numbers. On a second
sheet i have a "calendar" with week numbers as an overview.

What i'm trying to do now, is to all the transactions in the same week
to be placed under the correct week on the 2nd sheet. And i only have
partial success with a CASE select.
What i have so far works - it put the right data under the right weeks,
but because of the row-counter i use, every row in the next column
start where the previous one ends.

any help on this would be greatly appreciated.

My data looks like this:
A B I K
date amount code week
8/30/2006 -9.91 a 35
8/30/2006 -32.61 b 35
8/30/2006 -499.8 c 35
8/30/2006 250 d 35
8/31/2006 -12.97 e 35
8/31/2006 -150 f 35
8/31/2006 -9.72 g 35
8/31/2006 -49.95 h 35
8/31/2006 -2.9 I 35
9/1/2006 -102 k 35
9/1/2006 -50 l 35
9/1/2006 -47.61 m 35
9/1/2006 -50 n 35
9/7/2006 182.32 o 36
9/7/2006 -10 p 36


what i have so far:

Sub back()
worksheet("overview").activate
last = WorksheetFunction.CountA(Worksheets("data").Range( "A:A"))
rownum = 2

For Each week In Worksheets("data").Range("K1:K" & laatste)
numitems =
WorksheetFunction.CountIf(Worksheets("data").Range ("K1:K" & laatste),
week.Value)
If rownum = numitems Then
adres = Application.Match(week, Rows("1:1"))
Select Case week
Case week.Value
Worksheets("data").Range(week.Address).Copy
Range(Cells(rownum, adres), Cells(rownum, adres))
End Select
row = rownum + 1
Next week

End Sub

Ooohhww, thanks for that reply Jim!

Never thought off those, opend a whole new world! Cheers!
Jim Thomlinson schreef:

Why not avoid the code and just use a pivot table??? It would be a whole pile
easier and a lot more flexible...
--
HTH...

Jim Thomlinson


" wrote:

Hello There,

Im working on a macro thats organizes my download bank-statements into
a per-week view. The goal being that i have an easy overview which
weeks where expensive, and which werent (and weeks which weeks are
going to be expensive..)
I got as far as sorting the data in columns, with dates for each
transaction. From the dates i calculate weeks numbers. On a second
sheet i have a "calendar" with week numbers as an overview.

What i'm trying to do now, is to all the transactions in the same week
to be placed under the correct week on the 2nd sheet. And i only have
partial success with a CASE select.
What i have so far works - it put the right data under the right weeks,
but because of the row-counter i use, every row in the next column
start where the previous one ends.

any help on this would be greatly appreciated.

My data looks like this:
A B I K
date amount code week
8/30/2006 -9.91 a 35
8/30/2006 -32.61 b 35
8/30/2006 -499.8 c 35
8/30/2006 250 d 35
8/31/2006 -12.97 e 35
8/31/2006 -150 f 35
8/31/2006 -9.72 g 35
8/31/2006 -49.95 h 35
8/31/2006 -2.9 I 35
9/1/2006 -102 k 35
9/1/2006 -50 l 35
9/1/2006 -47.61 m 35
9/1/2006 -50 n 35
9/7/2006 182.32 o 36
9/7/2006 -10 p 36


what i have so far:

Sub back()
worksheet("overview").activate
last = WorksheetFunction.CountA(Worksheets("data").Range( "A:A"))
rownum = 2

For Each week In Worksheets("data").Range("K1:K" & laatste)
numitems =
WorksheetFunction.CountIf(Worksheets("data").Range ("K1:K" & laatste),
week.Value)
If rownum = numitems Then
adres = Application.Match(week, Rows("1:1"))
Select Case week
Case week.Value
Worksheets("data").Range(week.Address).Copy
Range(Cells(rownum, adres), Cells(rownum, adres))
End Select
row = rownum + 1
Next week

End Sub