Move items into weekly calendar

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

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?

--
Regards,
Tom Ogilvy


"normajmarsh" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

"Tom Ogilvy" wrote:

> 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?
>
> --
> Regards,
> Tom Ogilvy
>
>
> "normajmarsh" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

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

--
Regards,
Tom Ogilvy


"normajmarsh" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Tom Ogilvy" wrote:
>
> > 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?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "normajmarsh" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

Thanks Tom.

It worked like a charm.

Norma

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "normajmarsh" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "normajmarsh" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>