Auto Insert Row before Monday with Formula & formatting

[vaibhavc]

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.

 

[vaibhavc]

It are very close to now,

1. Row inserted with date is ok but it copies other contents from above row.. we want only date in that column & formulas, validation, formatting, conditional formatting for total row.
2. There is one more point every time we close workbook it inserts same rows again & again.
3. at the time of closing book excel always ask "There is a large amount of information on the clipboard. Do you want to able to paste this info. into another program later?

 

[Michael M]

Row inserted with date is ok but it copies other contents from above row.. we want only date in that column & formulas, validation, formatting, conditional formatting for total row. What other contents are in the row above...
There is one more point every time we close workbook it inserts same rows again & again.In that case you ned a counter in an empty cell, say A1. If tha's acceptable, I'll modify the code to suit
at the time of closing book excel always ask "There is a large amount of information on the clipboard. Do you want to able to paste this info. into another program later? code adjusted to fix this

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
Application.CutCopyMode = False
End Sub

 

[vaibhavc]

1) Above row contents means all cell contents same as above row. you can say its copy of above row. other cells are names, amts, other dates, & many cells containing formulas related to other cells.
2) I really don't have have programming knowledge & VBA, so don't know what is counter, in A columns i have sr no.

 

[Michael M]

1) Above row contents means all cell contents same as above row. you can say its copy of above row. other cells are names, amts, other dates, & many cells containing formulas related to other cells.... it does copy the same as above row....do you only want the cell from above not the entire row ??
2) I really don't have have programming knowledge & VBA, so don't know what is counter, in A columns i have sr no.a counter simply puts a number into a cell somewhere on the page. The next time you close the workbook, if it finds a value in the cell, it doesn't run the macro, but I need to know where to put it !!!

 

[vaibhavc]

1) i don't want to copy above row but insert a new row with above row formulas. and that Sunday date will be in date column into newly inserted row.
2) for counter you want a complete column or only one cell ? If you want only one cell then you can take XFD1 ( last columns first cell) or if you want column then take XFD column .

My current data range is A1:AZ500 ..may be will increase by 10 columns. so you can take counter place as u wish. Just let me know.

 

[Michael M]

The code will now only run once...with a "stop" text in cell XFD1
But I'm afraid, you've completely lost me with the row information......you want a row inserted with formulas, validations, formatting, etc...from the above row ??
So, that to me seems like copy and insert from the row above..!!!
You'll obviously have to point out the difference, because I'm at a loss with the final issue

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long, r As Long
If Range("XFD1").Value = "Stop" Then Exit Sub
Range("XFD1").Value = "Stop"
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 + 1).Insert
        Range("H" & r + 1).Value = Range("H" & r).Value + (7 - Application.WorksheetFunction.Weekday(Range("H" & r).Value) + 1)
    End If
Next r
Application.CutCopyMode = False
End Sub

 

[vaibhavc]

sorry to confusing you i must have to say copy only formulas, formatting, etc but not the text & other information i have filled in above row.

This time accurately inserted a row now only things remains, the inserted rows must have formulas, formatting,cf, validation as above row.

 

[Michael M]

Ok, try this...
Remember to remove "stop" text from cell xfd1, if you have run this before

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lr As Long, r As Long
Application.ScreenUpdating = False
If Range("XFD1").Value = "Stop" Then Exit Sub
Range("XFD1").Value = "Stop"
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
            With Rows(r + 1)
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteFormulas
                .PasteSpecial Paste:=xlPasteValidation
            End With
        Range("H" & r + 1).Value = Range("H" & r).Value + (7 - Application.WorksheetFunction.Weekday(Range("H" & r).Value) + 1)
    End If
Next r
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[vaibhavc]

my database is increasing daily. my office user is entering daily data into that sheet, Should he have to remove STOP every time? Also when removed STOP then it will insert same row again & again.

Also as per my last post i want only formulas are to copied from above row into newly inserted row. It is coping all texts, numbers from above row

 

[Michael M]

Ok, if you want to use the workbook on a regular basis.....I'd suggest you remove "stop" once a day, OR run the macro manually using ALT + F8, when required

To not have the text / numbers in the cells you will have to provide the cell ranges that are text / numbers only.....and the macro can clear those cells.
for example columns "A" to "G", "R" to "Z", etc
Otherwise, a blank row will have to be inserted and all the formulas, formatting, etc will have to be reinserted programatically.