Insert a Row bases on Column Value & Copy Down values

[Anil98765]

I have a excel with the following columns and rows. I insert the column "Cnt" manually to get a count of how many rows I need to insert. I created the VBA to insert and copy down the rows, except I am unable to get it to ADD 1 to the date for each new line.

I show the "Before" of the raw data and the "After" on how there are new rows inserted with the date increment by 1.

Before
Cnt UPI ENDDA BEGDA AWART ABRTG ABRST KALTG Absence Code
1 10142051 20150202 20150201 0100 2 15 2 Leave
5 10142392 20150227 20150223 0100 2 15 2 Leave
3 10142392 20150520 20150518 0100 12 90 17 Leave
5 10142392 20150828 20150824 0100 5 38 5 Leave

Cnt UPI ENDDA BEGDA AWART ABRTG ABRST KALTG Absence Code
1 10142051 20150202 20150202 0100 2 15 2 Leave
1 10142392 20150223 20150223 0100 2 15 2 Leave
10142392 20150224 20150224 0100 2 15 2 Leave
10142392 20150225 20150225 0100 2 15 2 Leave
10142392 20150226 20150226 0100 2 15 2 Leave
10142392 20150227 20150227 0100 2 15 2 Leave
1 10142392 20150518 20150518 0100 12 90 17 Leave
10142392 20150519 20150519 0100 12 90 17 Leave
10142392 20150520 20150520 0100 12 90 17 Leave
1 10142392 20150824 20150824 0100 5 38 5 Leave
10142392 20150825 20150825 0100 5 38 5 Leave
10142392 20150826 20150826 0100 5 38 5 Leave
10142392 20150827 20150827 0100 5 38 5 Leave
10142392 20150828 20150828 0100 5 38 5 Leave


I am looking for a way to add the +1 to each copied line and it would be great if it didn't go past the month. I.E. 20150132, but not a issue as the adding 1 is what I'm stuck on.

Any and all help would be great and attached a file with the CODE. You can run it on Sheet 1.



CODE BELOW
''''''''''''''''''''
Sub Insert_Rows_and_Copy_Dropdowns()


Dim lngCounter As Long

Const cstrCOL As String = "A"
Const clngSTART As Long = 2

For lngCounter = Range(cstrCOL & Rows.count).End(xlUp).Row To clngSTART Step -1
With Cells(lngCounter, cstrCOL)
If IsNumeric(.Value) And .Value > 1 Then
With .Offset(1, 0).Resize(.Value - 1, 1)
.EntireRow.Insert
End With
.Offset(0, 1).Resize(1, 1).Copy Destination:=.Offset(1, 1).Resize(.Value - 1, 1)
.Offset(0, 2).Resize(1, 1).Copy Destination:=.Offset(1, 2).Resize(.Value - 1, 1)
.Offset(0, 3).Resize(1, 1).Copy Destination:=.Offset(1, 3).Resize(.Value - 1, 1) 'Need to Add 1 to this

.Offset(0, 4).Resize(1, 1).Copy Destination:=.Offset(1, 4).Resize(.Value - 1, 1)
.Offset(0, 5).Resize(1, 1).Copy Destination:=.Offset(1, 5).Resize(.Value - 1, 1)
.Offset(0, 6).Resize(1, 1).Copy Destination:=.Offset(1, 6).Resize(.Value - 1, 1)
.Offset(0, 7).Resize(1, 1).Copy Destination:=.Offset(1, 7).Resize(.Value - 1, 1)
.Offset(0, 8).Resize(1, 1).Copy Destination:=.Offset(1, 8).Resize(.Value - 1, 1)



End If

End With
Next lngCounter
End Sub

[Bernie Deitrick]

After this line:

Please Login or Register  to view this content.

Add this

Please Login or Register  to view this content.

[Anil98765]

After this line:

Please Login or Register  to view this content.

Add this

Please Login or Register  to view this content.

Wow, thank you very much - Your code put a 1,2,3 etc into each one of the rows, but gave me the direction I needed. I changed it slightly. It add 1 to the date so the date increments. 20150101, 20150102, 20150103, etc

Please Login or Register  to view this content.

Much appreciated for your help!

[Bernie Deitrick]

Cool - happy to hear that you got it to work.