Move items into weekly calendar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

normajmarsh - 23 Jan 2006 19:49 GMT

Could someone help me with some sample code for this:

I have rows of data in a sheet with the Date/time in the first column (five
columns total for each row of data - including the date/time column).

I need to copy the data into a 7-day weekly calendar format with the time
and other four data columns listed under each day.

I think that I can format a sheet to look like a 7-day calendar (with five
columns for each day and each successive block of columns representing the
next day).

But, I am having trouble with the code to move through the data sheet and
copy the data to the calendar sheet.

Thanks

Norma

Reply to this Message

Tom Ogilvy - 23 Jan 2006 20:02 GMT

will specific rows be designated for specific times or do you just want a
contiguous list under each day?

Is the data on one sheet and the calendar on the other - what are the sheet
names

What is the first day in the calendar (Sunday, Monday ?)
where is this first column (column A, B, ?). Where does the first data
go - what row? If not just a contiguous list of events, will the time in
the data match the time for the rows. (no situation where 9:15 goes into
the row for 9:00 )

Will the data to be spread just include data for that 7 day period or are
you speading a subset of the data?

Signature

Regards,
Tom Ogilvy

> Could someone help me with some sample code for this:
>
[quoted text clipped - 14 lines]
>
> Norma

Reply to this Message

normajmarsh - 24 Jan 2006 00:43 GMT

There could be multiple items with the same time so, I was going to list them
under each day (like a task list in chronological order).

I have the data in Sheet1 and the calendar setup in Calendar1.

The calendar is one week starting with Sunday (I set the start date in the
A1 cell of the calendar1 sheet). The data for the first day starts in
A2,B2,C2,D2,E2 - five columns and the next data row for that day starts in
A3, etc.
The next day's data would start in F2 - J2, F3-J3, and etc.

The weeks data will be a subset of a larger list if data (I am trying to
display a one week task list out of a month or more's list of tasks. Some
tasks may be scheduled prior to the first day in the week's display and some
may be scheduled after the last day in the week's displayed).

I can do the formating on the Calendar page but the VB sorting and copying
code is confusing to me.

If you could point me in the right direction on this, I think that I could
work it out.

Thanks
Norma

> will specific rows be designated for specific times or do you just want a
> contiguous list under each day?
[quoted text clipped - 30 lines]
> >
> > Norma

Reply to this Message

Tom Ogilvy - 24 Jan 2006 02:50 GMT

Based on my understanding of your data layout, this worked for me:

Sub abc()
Dim sh As Worksheet
Dim dt As Date, dt1 As Date
Dim rng As Range, rng1 As Range
Dim cell As Range, offset As Long
Set sh = Worksheets("Calendar1")
dt = Int(sh.Range("A1"))
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), _
.Cells(2, 1).End(xlDown))
End With
For Each cell In rng
dt1 = Int(cell.Value)
If dt1 >= dt And dt1 <= dt + 6 Then
offset = (dt1 - dt) * 5
Set rng1 = sh.Cells(Rows.Count, _
offset + 1).End(xlUp)(2)
cell.Resize(1, 5).Copy _
Destination:=rng1
End If
Next
End Sub

Signature

Regards,
Tom Ogilvy

> There could be multiple items with the same time so, I was going to list them
> under each day (like a task list in chronological order).
[quoted text clipped - 55 lines]
> > >
> > > Norma

Reply to this Message

normajmarsh - 24 Jan 2006 14:13 GMT

Thanks Tom.

It worked like a charm.

Norma

> Based on my understanding of your data layout, this worked for me:
>
[quoted text clipped - 91 lines]
> > > >
> > > > Norma

Reply to this Message