Move items into weekly calendar

[Guest]

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

 

[Tom Ogilvy]

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?

 

[Guest]

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]

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

 

[Guest]

Thanks Tom.

It worked like a charm.

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