Create out of one sheet (month) , 4 or 5 new sheets (weeks). How to do that?

[JulianNL]

Hello together,

I'm new here and I hope you can help me. I already search a lot, but I can't find any sufficient answer.


I have a lot of data (over 650 rows) and every row has more than 50 columns, so I really want to seperate the data.
The file is exported from a system which records many different things, in my case the start and the end and the time between, of a "support call".
So now I want to create a macro that can...
- identify how much weeks the selected month has and which dates are related to every week
- seperate the month data in 4 or 5 new sheets and fill the data of the week in the sheet

[TABLE="width: 544"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Number
[/TD]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Dif
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]000053413
[/TD]
[TD]1-8-2013 8:36
[/TD]
[TD]1-8-2013 9:23
[/TD]
[TD]0:46:54
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]000053414
[/TD]
[TD]1-8-2013 8:42
[/TD]
[TD]1-8-2013 9:07
[/TD]
[TD]0:24:56
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]000053448
[/TD]
[TD]2-8-2013 14:53
[/TD]
[TD]2-8-2013 15:14
[/TD]
[TD]0:21:11
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]000053450
[/TD]
[TD]2-8-2013 14:58
[/TD]
[TD]2-8-2013 15:28
[/TD]
[TD]0:30:14
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]000053451
[/TD]
[TD]5-8-2013 8:06
[/TD]
[TD]5-8-2013 9:49
[/TD]
[TD]1:42:36
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]000053486
[/TD]
[TD]6-8-2013 8:32
[/TD]
[TD]6-8-2013 13:30
[/TD]
[TD]4:57:31
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]000053510
[/TD]
[TD]7-8-2013 9:01
[/TD]
[TD]7-8-2013 9:29
[/TD]
[TD]0:28:00
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]000053538
[/TD]
[TD]8-8-2013 9:22
[/TD]
[TD]8-8-2013 11:23
[/TD]
[TD]2:00:56
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]000053572
[/TD]
[TD]9-8-2013 9:16
[/TD]
[TD]9-8-2013 10:08
[/TD]
[TD]0:51:14
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]000053589
[/TD]
[TD]12-8-2013 8:10
[/TD]
[TD]12-8-2013 8:20
[/TD]
[TD]0:10:29
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]000053629
[/TD]
[TD]13-8-2013 8:15
[/TD]
[TD]13-8-2013 10:25
[/TD]
[TD]2:10:38
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]000053665
[/TD]
[TD]14-8-2013 9:08
[/TD]
[TD]14-8-2013 9:29
[/TD]
[TD]0:21:25
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]000053695
[/TD]
[TD]15-8-2013 13:35
[/TD]
[TD]15-8-2013 14:27
[/TD]
[TD]0:52:16
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]000053717
[/TD]
[TD]16-8-2013 11:44
[/TD]
[TD]16-8-2013 11:55
[/TD]
[TD]0:11:20
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]000053728
[/TD]
[TD]19-8-2013 8:25
[/TD]
[TD]19-8-2013 8:35
[/TD]
[TD]0:09:53
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]000053757
[/TD]
[TD]20-8-2013 8:33
[/TD]
[TD]20-8-2013 8:36
[/TD]
[TD]0:02:52
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]000053786
[/TD]
[TD]21-8-2013 8:06
[/TD]
[TD]21-8-2013 9:34
[/TD]
[TD]1:27:36
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]000053820
[/TD]
[TD]22-8-2013 9:06
[/TD]
[TD]22-8-2013 9:09
[/TD]
[TD]0:03:27
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]000053856
[/TD]
[TD]23-8-2013 10:15
[/TD]
[TD]23-8-2013 10:44
[/TD]
[TD]0:29:13
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]000053879
[/TD]
[TD]26-8-2013 8:41
[/TD]
[TD]26-8-2013 9:36
[/TD]
[TD]0:55:08
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]000053914
[/TD]
[TD]27-8-2013 8:40
[/TD]
[TD]27-8-2013 9:57
[/TD]
[TD]1:17:15
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]000053939
[/TD]
[TD]28-8-2013 9:15
[/TD]
[TD]28-8-2013 10:58
[/TD]
[TD]1:42:49
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]000053967
[/TD]
[TD]29-8-2013 9:09
[/TD]
[TD]29-8-2013 9:42
[/TD]
[TD]0:33:27
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]000054009
[/TD]
[TD]30-8-2013 15:07
[/TD]
[TD]30-8-2013 15:51
[/TD]
[TD]0:44:12
[/TD]
[/TR]
</tbody>[/TABLE]



Additional Question:

How can I automatically define the range in a the new sheet after the macro copied an unknown amount of rows into it?
Or how can I locate the last filled cell (=D? e.g row 5) and can write a formula which sum/count etc. 2 rows after the last filled cell (=D?+2 row 7)?

Range("D?+2").Select
ActiveCell.Formula = "=SUM(D2:D?)"

[TABLE="width: 544"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Number
[/TD]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Dif
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]000053413
[/TD]
[TD]1-8-2013 8:36
[/TD]
[TD]1-8-2013 9:23
[/TD]
[TD]0:46:54
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]000053414
[/TD]
[TD]1-8-2013 8:42
[/TD]
[TD]1-8-2013 9:07
[/TD]
[TD]0:24:56
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]000053448
[/TD]
[TD]2-8-2013 14:53
[/TD]
[TD]2-8-2013 15:14
[/TD]
[TD]0:21:11
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]000053450
[/TD]
[TD]2-8-2013 14:58
[/TD]
[TD]2-8-2013 15:28
[/TD]
[TD]0:30:14
[/TD]
[/TR]
</tbody>[/TABLE]
6
7 Sum(D2:D?)

Thank you very much.