Auto Insert Row before Monday with Formula & formatting

vaibhavc

New Member
Joined
Jun 21, 2014
Messages
27
Hi...

In column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14

21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with <acronym>VBA</acronym> (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
Please note I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future…please help me.

Thanks in Advance.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Ok... I got a code but here is some error ( run time error 13 Type mismatch) & also there is need some up gradation in code.


Code:
[COLOR=#333333]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">With Sheets("payment collection")Dim i As LongDim rcell As RangeFor i = Range("H" & Rows.Count).End(3)(1).Row To 1 Step -1    If Weekday(CDate(Range("H" & i))) = 2 Then        Range("H" & i).EntireRow.Insert xlDown    End IfNext iFor Each rcell In Range("H1:H" & Range("H" & Rows.Count).End(3)(1).Row)    If rcell.Value = "" Then        Cells(rcell.Row + 1, "H").Copy        Cells(rcell.Row, "H").PasteSpecial xlPasteFormulasAndNumberFormats    End IfNext rcellEnd With </code></pre>[COLOR=#333333]End Sub[/COLOR]

Currently using this code row is inserted before each Monday & if there is no Monday date then it is not inserting row.

WHAT I WANT -

1) I want to be row inserted between ( Saturday or less then which date is there) & (Monday or greater than which date is there.)
2) the new row inserted has to be date filled automatically which is Sunday date
3) When inserting row it should copy the above row formulas, formatting, conditional formatting, data validation, etc.

Thanks in advance.
 
Upvote 0
Not sure if this is what you want, it's UNTESTED, but try
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long, r As Long
lr = Sheets("payment collection").Cells(Rows.Count, "H").End(xlUp).Row
 For r = lr To 2 Step -1
    If Application.WorksheetFunction.Weekday(Range("H" & r).Value) = 7 Then
        Rows(r).Copy
        Rows(r + 1).Insert
    End If
Next r
End Sub
 
Upvote 0
Thanks for reply...

i have run your code but blank row is inserted at top of database(below the header row) also somewhere in middle. I think there need some up gradation.

I am not able to attach here sample file otherwise I would attach it.

Please test it & send me...
 
Upvote 0
What are the dates at the insertion point that doesn't work.....it runs OK for me for 100 rows !!
 
Upvote 0
I am sorry to bother you with this, but i'm kind of stuck and looking for assistance. i am trying to illustrate to you...

11-Jun-14
12-Jun-14

15-Jun-14 i want to be row added here 12th is Thursday & 16th is Monday

16-Jun-14
16-Jun-14
17-jun-14
18-Jun-14
21-Jun-14

22-Jun-14 i want to be row added here because 21st Jun is Saturday & 23 Jun is Monday

23-Jun-14
25-Jun-14
28-Jun-14
27-Jun-14
29-Jun-14 i want to be row added here because 27th jun is Friday & 2 Jul is Wednesday

2-Jul-14
2-Jul-14


I want to be only one row inserted between ( Saturday or less then which date is there) & (Monday or greater than which date is there.) as shown above.

T
he new row inserted has to be date filled automatically which is Sunday date

When inserting row it should copy the above row formulas, formatting, conditional formatting, data validation, etc.
 
Upvote 0
It's going to really hard to provide this if the dates in ascending order....it doesn't follow any logical pattern !!
see your example....
25 jun
28 jun
27 jun

How is Excel going to know what logic to follow
 
Upvote 0
Ah, OK, well try this....on a test worksheet !!
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long, r As Long
lr = Sheets("payment collection").Cells(Rows.Count, "H").End(xlUp).Row
 For r = lr To 2 Step -1
    If Application.WorksheetFunction.Weekday(Range("H" & r + 1).Value) < Application.WorksheetFunction.Weekday(Range("H" & r).Value) Then
        Rows(r).Copy
        Rows(r + 1).Insert
        Range("H" & r + 1).Value = Range("H" & r).Value + (7 - Application.WorksheetFunction.Weekday(Range("H" & r).Value) + 1)
    End If
Next r
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top