Articles

excel copy row increment date

August 31, 2009 at 08:35:45
Specs: Windows vista

I'm trying to copy 1 row to the row below and additionally increment 1 cell (filled with a date) by 1 week - meaning exactly the same row, only the cell filled with the date needs to be incremented. (In this cas in column "C") - Any suggestions for the vba code?

See More: excel copy row increment date

Report •


#1
August 31, 2009 at 09:05:12

Would you be willing to try it with a formula instead of VBA?

Enter this in rows 1 & 2, and then from now on you can just grab the autofill handle and fill down to increment the date.

	
     A	   B	     C          D	   
1  Data1  Data2	 8/31/2009    Data3
2  Data1  Data2	  =D1+7	     Data3

If you need to lock the data so it doesn't increment also, you could use:

     A	   B	     C          D	   
1  Data1  Data2	 8/31/2009    Data3
2  =$A$1  =$B$1	  =D1+7	     =$D$1

The only thing that will change is the date.


Report •

#2
August 31, 2009 at 11:51:38

Thank you very much for the speedy reply.
The problem is that the date value can not be relative to other cells as the data list changes regularly. It realy needs to be a vba script. ( I tried with recording a macro, but no dice) - Hope you can help me with this. Again thanks in advance.

Report •

#3
August 31, 2009 at 13:10:13

I must be missing something...

In your OP you said you wanted to "increment 1 cell (filled with a date) by 1 week" but now you say that "the date value can not be relative to other cells".

If the date is incremented by 7 days, then it must be relative to other cells. How would it know what value to start with before it is incremented by 7 days?

You also said you wanted to "copy 1 row to the row below...meaning exactly the same row" but now you say "the data list changes regularly."

Perhaps you can provide a before and after example similar to mine so we can clear up my confusion.


Report •

Related Solutions

#4
August 31, 2009 at 22:58:29

This is only a small proces in a bigger project. First I need to fill a list in a table afterwards it is possible that certain dates change. If I have a solution for copying 1 line, I can change the code in repeating that for a few hundred lines based upon another value. I realy need to have it in vba. The code to copy the line I already have:

ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow

The only thing is incrementing column "C" with 1 week.
The example you gave is good. Only instead of C1+7, it needs to be a real date value.

I hope I've informed you a bit better.

Best regards


Report •

#5
September 1, 2009 at 04:28:03

re: it needs to be a real date value.

You do realize that if you add 7 to a date it will return a date that is 1 week later, right?

Assuming what you have tried already is the most efficient way, which is hard to say without more detail about the "bigger project", try this.

Sub CopyRowIncDate()
 ActiveCell.Offset(1, 0).EntireRow.Insert
 ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
 Range("C" & ActiveCell.Offset(1, 0).Row) = _
    Range("C" & ActiveCell.Row) + 7
End Sub

This should put "a real date value" in Column C.


Report •

#6
September 1, 2009 at 04:55:03

This is exactly what I was looking for.
Many thanks and again thanks for the speedy reply.

Report •

#7
September 23, 2009 at 00:48:05

Just following your thread there about copying and inserting
rows as per a condition. Sorry to innundate you with a similar
question, but I have a similar problem to solve but with a slight
twist and that is

I need the macro to work out the interval between two dates
(start & End date) which I input and then accordingly
duplicate the record that many times so lets

the number of days between 23/09 and 25/09 is 3 (inclusive of
25th) then I need the macro to first copy and insert three rows
of the same data lets say which is on row 16, except that the
start date on each of those 3 rows increments until it is equal
to the end date.

It's a bit of twister for me as a complete newbie, I have been
getting along all this while recording macros, but this one will
not take a mouse click for an answer. Any help in this regard
will be greatly appreciated.

Thanks in advance
Alok


Report •

#8
September 23, 2009 at 04:14:02

Hi,

A date difference in a macro is just a simple subtraction.

Excel holds dates as whole numbers (with times as decimal part of number)

For example put dates in cells A2 and B2

	A		B
1	Start		Finish
2	23-Sep-09	25-Sep-09

Use the following code to get the number of days:
Sub DateDiff()
Dim dblDateDiff As Double

With ActiveSheet
    dblDateDiff = Int(.Range("B2").Value2) - Int(.Range("A2").Value2) + 1
End With
End Sub

The int function returns the integer part of the date value, just in case there was time information stored in A2 or B2.
Value2 returns the number Excel stores for dates, in this case 40079 and 40081

Regards


Report •

#9
September 23, 2009 at 06:18:30

Thanks it works but only for one cell.

at the risk of sounding out of it... I'm pasting here the inital
data and the final output what I'm looking for.

This will be the 1st row of records
Start Date End Date Start Time End Time Group Sub Group
Details Manager
23-09-09 25-09-09 10:00 AM 11:00 AM Class X section 1
Goes for preparation Teacher 1

On the basis of the start and end date above I need the final
output to look like this when I click a command button

Start Date End Date Start Time End Time Group Sub
Group Details Manager
23-09-09 25-09-09 10:00 AM 11:00 AM Class X section A
preparation Teacher 1
24-09-09 25-09-09 10:00 AM 11:00 AM Class X section A
preparation Teacher 1
25-09-09 25-09-09 10:00 AM 11:00 AM Class X section A
preparation Teacher 1


with the change being that the start dates will increment and
show +1 day until it becomes equal to the end date.

Thanks again
alok


Report •

#10
September 23, 2009 at 06:36:36

Hi,

The subroutine I sent you was just so that you could create the counter for each record.

Use the result to control a For Next which copies the data to subsequent rows and provides a value to increment the date.
23-Sep-09 +1 = 24-Sep-09

Regards


Report •

#11
September 23, 2009 at 07:11:00

Hi,

Here is a simple for next loop that will copy one initial row of data, the number of times specified by the date range, and increment the dates.

Row 2 cells A2 to G2 look like this

	A		B		C	D	E	F		G
2	23-Sep-09	25-Sep-09	10:00	11:00	Class X	Section 1	Teacher 1

Run this macro

Sub Extender()
Dim dblDateDiff As Double
Dim n As Integer

With ActiveSheet
    dblDateDiff = Int(.Range("B2").Value2) - Int(.Range("A2").Value2) + 1
    For n = 1 To dblDateDiff
        .Range("C2:G2").Copy Destination:=.Range("C2:G2").Offset(n, 0)
        .Range("B2").Offset(n, 0).Value = .Range("A2").Value + n - 1
    Next n
End With
End Sub

and the output is:

	A		B		C	D	E	F		G
2	23-Sep-09	25-Sep-09	10:00	11:00	Class X	Section 1	Teacher 1
3			23-Sep-09	10:00	11:00	Class X	Section 1	Teacher 1
4			24-Sep-09	10:00	11:00	Class X	Section 1	Teacher 1
5			25-Sep-09	10:00	11:00	Class X	Section 1	Teacher 1

Regards


Report •

#12
September 23, 2009 at 07:27:29

Thanks again,

Am afraid don't know how to code, like I said have recorded
macros until now, so am using all you good people's input to
make this work.

Currently I'm using this code, which asks me in a dialog box
the number of days to add. which it does beautifully

Private Sub CommandButton8_Click()
Range("A16").Select
numrows = InputBox("Number of Days to add")
'Rows(16.16).Copy ActiveCell.Resize(numrows)
Rows(16.16).Copy
Range("A16").Select
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
Rows("16:16").Select
Selection.ClearContents
Range("A16").Select
End Sub

Now if you could guide me on how to incorporate your
subroutine so that the date increment function works, would
greatly appreciate it.

thanks


Report •

#13
September 23, 2009 at 07:47:50

Hi,

Now I am confused.

You said that the number of copies of the data depends on the range of dates, but your code which is run from a button on your spreadsheet asks for input of a number of days.

How does the number of days relate to the two dates at the start of each record?

Regards


Report •

#14
September 23, 2009 at 07:50:15

sorry didn't see your last post before I put in my reply.Will try the
code and let you know.

Thanks


Report •

#15
September 23, 2009 at 08:35:55

That worked beautifully, I tweaked it a bit to get the changed
values of the date back to column A which is what is required.

Just one more thing if you can help out and I can seal this
one. Instead of merely copying I need it to insert and copy
the adequate number of rows to fit in the number of days and
also to return

I've pasted my code below which is working currently perfectly
for copy.

Private Sub CommandButton8_Click()
Dim dblDateDiff As Double
Dim n As Integer

With ActiveSheet
dblDateDiff = Int(.Range("B16").Value2) -
Int(.Range("A16").Value2) + 1
For n = 1 To dblDateDiff
.Range("B16:J16").Copy
Destination:=.Range("B16:J16").Offset(n, 0)
.Range("A16").Offset(n, 0).Value = .Range("A16").Value
+ n - 1
Next n
End With
Rows("16:16").Select
Selection.ClearContents
Range("A16").Select
End Sub

Many Thanks
& warm Regards


Report •

#16
September 23, 2009 at 09:22:32

Hi,

You can modify the code by inserting a new row and shifting everything down with this extra line of code

        .Range("2:2").Offset(n, 0).Insert Shift:=xlShiftDown

This goes before the copy line

If you add a further insert you can have a blank row between each set of data

This was the output - run twice, changing the teacher number.

23-Sep-09	26-Sep-09	14:00	15:00	Class X	Section 1	Teacher 2
		23-Sep-09	14:00	15:00	Class X	Section 1	Teacher 2
		24-Sep-09	14:00	15:00	Class X	Section 1	Teacher 2
		25-Sep-09	14:00	15:00	Class X	Section 1	Teacher 2
		26-Sep-09	14:00	15:00	Class X	Section 1	Teacher 2
						
		23-Sep-09	14:00	15:00	Class X	Section 1	Teacher 1
		24-Sep-09	14:00	15:00	Class X	Section 1	Teacher 1
		25-Sep-09	14:00	15:00	Class X	Section 1	Teacher 1
		26-Sep-09	14:00	15:00	Class X	Section 1	Teacher 1

Revised code

Sub Extender()
Dim dblDateDiff As Double
Dim n As Integer

With ActiveSheet
    dblDateDiff = Int(.Range("B2").Value2) - Int(.Range("A2").Value2) + 1
    .Range("3:3").Insert Shift:=xlShiftDown
    For n = 1 To dblDateDiff
        .Range("2:2").Offset(n, 0).Insert Shift:=xlShiftDown
        .Range("C2:G2").Copy Destination:=.Range("C2:G2").Offset(n, 0)
        .Range("B2").Offset(n, 0).Value = .Range("A2").Value + n - 1
    Next n
End With
End Sub


Report •

#17
September 23, 2009 at 10:01:19

Thanks Humar for all your help.

That worked like a dream. :)
Cheers
Alok


Report •

#18
September 23, 2009 at 10:09:44

Your welcome

Report •


Ask Question