Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move items into weekly calendar
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move items into weekly calendar
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move items into weekly calendar
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move items into weekly calendar
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up a bi-weekly calendar event | Excel Discussion (Misc queries) | |||
how to ganerate the weekly calendar | New Users to Excel | |||
import calendar items from excel into outlook calendar | Excel Discussion (Misc queries) | |||
need to make a template for weekly calendar ASAP that shows pictu. | Excel Discussion (Misc queries) | |||
Weekly data into Calendar Weeks | Excel Worksheet Functions |