Hello

I am using Excel 2003.

I have a table showing meetings. This includes StartDate, EndDate,
Information (about the meeting) and Room. In most cases, the
StartDate=EndDate.

However, for those meetings that take more than one day, I need to have an
entry for each day so that a daily schedule of "what's on" can be printed.

I need to insert additional lines for the additional days, and copy the
information from the original entry into the new lines. I then have to change
the StartDate and EndDate for each day.

Example- if the meeting started on October 1 and finished October 3, the
original entry is:

StartDate EndDate Information Room
October 1 October 3 Bla bla bla 301

The new entries should be:
October 1 October 1 Bla bla bla 301
October 2 October 2 Bla bla bla 301
October 3 October 3 Bla bla bla 301

I have written the rest ... sorting by StartDate and grouping according to
this.

Any assistance for adding the lines, copying the data and changing the date
would be greatly appreciated!
--
Thanks very much. KDJ

KDJ wrote:
Hello

I am using Excel 2003.

I have a table showing meetings. This includes StartDate, EndDate,
Information (about the meeting) and Room. In most cases, the
StartDate=EndDate.

However, for those meetings that take more than one day, I need to have an
entry for each day so that a daily schedule of "what's on" can be printed.

I need to insert additional lines for the additional days, and copy the
information from the original entry into the new lines. I then have to change
the StartDate and EndDate for each day.

Example- if the meeting started on October 1 and finished October 3, the
original entry is:

StartDate EndDate Information Room
October 1 October 3 Bla bla bla 301

The new entries should be:
October 1 October 1 Bla bla bla 301
October 2 October 2 Bla bla bla 301
October 3 October 3 Bla bla bla 301

I have written the rest ... sorting by StartDate and grouping according to
this.

Any assistance for adding the lines, copying the data and changing the date
would be greatly appreciated!
--
Thanks very much. KDJ

Just right a sub that takes the end date and subtracts the startdate to
work out how many times it need to loop to insert the new rows. Take
the start date and add the counter to it to get the new start and end
date for fields 1 and 2 and then copy the room from the original cell.
I started to write the code and then couldn't rember the syntax for
offset and then realised you prob want date formatting etc.

Will write the code and test it and hopefully I'll post back before
someone else does, just a warning it might not look pretty.

Stopher

Try this:

Sub InsertRows()
Dim Startdate As Date
Dim EndDate As Date
Dim Gap
Dim Prow As Integer
Startdate = Range("A2").Value
EndDate = Range("B2").Value

Gap = EndDate - Startdate
Prow = 4
If Gap = 0 Then
Else
For i = 0 To Gap

Let Startdate = Startdate + i
Let Prow = Prow + 1
Cells(Prow, 1) = Startdate
Cells(Prow, 2) = Startdate
Cells(Prow, 4) = Range("D2")
Startdate = Range("A2").Value
Next
End If
End Sub

Just change the Prow to your starting row. It also assumes that the
Startdate, Enddate line starts in A2. You could curcumvent this by
giving the cells named ranges and then you could move them anywhere,
then just sub these in for Range("A2").value.

Hope this helps you out.

Stopher

Thanks so much. I'll try it out as soon as possible and let you know how I go.

KDJ

"Stopher" wrote:

Try this:

Sub InsertRows()
Dim Startdate As Date
Dim EndDate As Date
Dim Gap
Dim Prow As Integer
Startdate = Range("A2").Value
EndDate = Range("B2").Value

Gap = EndDate - Startdate
Prow = 4
If Gap = 0 Then
Else
For i = 0 To Gap

Let Startdate = Startdate + i
Let Prow = Prow + 1
Cells(Prow, 1) = Startdate
Cells(Prow, 2) = Startdate
Cells(Prow, 4) = Range("D2")
Startdate = Range("A2").Value
Next
End If
End Sub

Just change the Prow to your starting row. It also assumes that the
Startdate, Enddate line starts in A2. You could curcumvent this by
giving the cells named ranges and then you could move them anywhere,
then just sub these in for Range("A2").value.

Hope this helps you out.

Stopher

The code would be very much appreciated! I had offset and the counter in my
head but just can't put it together. I am too much of a novice.

The formatting is not a problem. I can solve that at the end by formatting
the report as a whole. Getting those lines in is where I am stuck.

Thank you!
KDJ

"Stopher" wrote:


KDJ wrote:
Hello

I am using Excel 2003.

I have a table showing meetings. This includes StartDate, EndDate,
Information (about the meeting) and Room. In most cases, the
StartDate=EndDate.

However, for those meetings that take more than one day, I need to have an
entry for each day so that a daily schedule of "what's on" can be printed.

I need to insert additional lines for the additional days, and copy the
information from the original entry into the new lines. I then have to change
the StartDate and EndDate for each day.

Example- if the meeting started on October 1 and finished October 3, the
original entry is:

StartDate EndDate Information Room
October 1 October 3 Bla bla bla 301

The new entries should be:
October 1 October 1 Bla bla bla 301
October 2 October 2 Bla bla bla 301
October 3 October 3 Bla bla bla 301

I have written the rest ... sorting by StartDate and grouping according to
this.

Any assistance for adding the lines, copying the data and changing the date
would be greatly appreciated!
--
Thanks very much. KDJ

Just right a sub that takes the end date and subtracts the startdate to
work out how many times it need to loop to insert the new rows. Take
the start date and add the counter to it to get the new start and end
date for fields 1 and 2 and then copy the room from the original cell.
I started to write the code and then couldn't rember the syntax for
offset and then realised you prob want date formatting etc.

Will write the code and test it and hopefully I'll post back before
someone else does, just a warning it might not look pretty.

Stopher

I think the code I posted is half what you wanted sorry, I read your
post again.

What you want it to do is keep adding rows to the bottom of the list
everytime you change the dates and run the macro, correct?