Add new row base on date.

newapa

Board Regular
Joined
Sep 13, 2012
Messages
69
Hi!

i'm trying to creat a macro that add new rows base on date.
evertime i hit ctrl+shift+A it will add new row to column A and B. and Column A will get this days of date.
and if it go to next month Column A,B,C and D will get new row as well. A get the days date. and Column C got the days Month(this mounth = 10). and Column D will Get the total of Column B base on Month.

the data is like this.

Code:
columnA       columnB        ColumnC       ColumnD
date          value             month           total
2012-02-05    255             2012-02        255
2012-01-05    254             2012-01        708
2012-01-02    154
2012-01-01    300

Code:
Sub NewDay()
' NewDay Macro
' Keyboard Shortcut: Ctrl+Shift+A


    Range("A2:B2").Copy
    Range("A2:B2").Insert shift:=xlDown, Copyorigin:=xlFormatFromRightOrBelow
    Range("A2:B2").PasteSpecial xlPasteFormulasAndNumberFormats
    Range("B2").ClearContents
    Range("A2").Value = Now()
    
    If DateSerial(Year(Date), Month(Date), Day("")) > Range("C2").DataSeries Then
        Range("C2:D2").Insert shift:=xlDown, Copyorigin:=xlFormatFromRightOrBelow
        Range("C2:D2").PasteSpecial xlPasteFormulasAndNumberFormats
        Range("C2").Value = DateSerial(Now(), Now(), "")
    End If
    
End Sub

so far i manage to add new row in column A and B but can't get the C to work.
i hope u guys understand what i mean and my skills on vba is very poor.

ths in advance
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
<br />
Book1
ABCD
1datevaluemonthtotal
22012-02-052552012-02255
32012-01-052542012-01708
42012-01-02154
52012-01-01300
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(TEXT($A$1:$A$5,"yyyy-mm")=C2),$B$1:$B$5)
D3=SUMPRODUCT(--(TEXT($A$1:$A$5,"yyyy-mm")=C3),$B$1:$B$5)


Code:
[color=darkblue]Sub[/color] NewDay()
[color=green]' NewDay Macro[/color]
[color=green]' Keyboard Shortcut: Ctrl+Shift+A[/color]
    
    Range("A2:B2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A2").Value = [color=darkblue]Date[/color]
        
    [color=darkblue]If[/color] Range("C2").Value <> Format(Date, "yyyy-mm") [color=darkblue]Then[/color]
        Range("C2:D2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
        Range("C2").Value = Format(Date, "yyyy-mm")
        Range("D2").FormulaR1C1 = Range("D3").FormulaR1C1
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Hi alphafrog!

thx for u help. the vba code was exellent.
but your sumproduct dosen't work. i get zero. but when i changed it to sum. its work. but its get the totoal of elle in column b.


best regard
newapa
 
Upvote 0
no i didn't change anything. :(

but when i do ctrl+shift+A add a new row will it expand the row??
just now it is A1:A5 if i add another row will the cod expand to like A1:A6?

i'm forget to say that i'm using excel 2010
thx
 
Upvote 0
but when i do ctrl+shift+A add a new row will it expand the row??
just now it is A1:A5 if i add another row will the cod expand to like A1:A6?

The Table in post #2 above is what it looks like before you run the macro. The table below is what it should look like after you run the macro.
<br />
Book1
ABCD
1datevaluemonthtotal
210/2/20122012-100
32/5/20122552012-02255
41/5/20122542012-01708
51/2/2012154
61/1/2012300
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=C2),$B$1:$B$6)
D3=SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=C3),$B$1:$B$6)
D4=SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=C4),$B$1:$B$6)

The date in A2 will be the current date. The value in B2 needs to be manually filled in. It adds a new row in C2:D2 if it is a new month. The total in D2 for a new month will show 0 until you add values for that month.
 
Upvote 0
ok thx alphafrog!

i have change the code to like this. =SUMPRODUCT(--(MONTH($A$1:$A$6)=MONTH($C2));($B$1:$B$6))
but i got the #value insted of 0. but u code =SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=C2),$B$1:$B$6) i got value 0

can u please explain or help me ot with this.

best regard
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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