Macro: Button click creates new blank row

[Cavill]

I am new to the VBA side of excel so basically I am a bit of a newbie. I don't know how to code in VBA yet but am attempting to learn.

I am creating a dynamic spreadsheet to record my stock portfolio.

I want to make a macro that creates a new row below Row 1 or above Row 2 (Always a new row in row 2 basically). I want the new row to have the same fill colour used in Row 3 and the same row height, ranging between columns A3-J3.
I also want this new row to include formulas used in cells J3, I3, G3, F3 and D3. Is this possible?

Additionally I want to create a button (I know how to create a button and assign a macro to it already) that is off to the side that is assigned to the macro that creates the new row, however I want the button fixed to its placed position, and doesnt move down as the new rows are created, anyone know how to do this?

 

[machopicho]

Maybe:

Sub Macro1()
    On Error Resume Next
    Rows("3:3").Copy
    Rows("2:2").Insert Shift:=xlDown
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[jim may]

Here's another version..

Where you data looks like this before running Macro (below):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	MyHeader1	MyHeader2	MyHeader3	MyHeader4	MyHeader5	MyHeader6	MyHeader7	MyHeader8	MyHeader9	MyHeader10
2	sdfg	gdf	654.00	1,308.00	9,879.00	3,293.00	327.00	654.00	4,939.50	654.00
3	fgh	fsd	12.00	35.00	65.00	456.00	2,345.00	354.00	3,456.00	567.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=C2*2
F2	=E2/3
G2	=C2/2
I2	=E2/2
J2	=D2/2

In a standard Module paste In:

Sub MyNewRow2()
    Application.ScreenUpdating = False
    Rows("2:2").Insert Shift:=xlDown   'Insert New Blank Row 2
    Rows("3:3").Copy Range("A2")    'Copy dtat from Old row 2 to new row 2
    Range("A2:J2").SpecialCells(xlCellTypeConstants, 23).ClearContents     'Clear all Constants, not formulas
    Range("A2").Select
    Application.ScreenUpdating = True
End Sub

It should Now Look like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	MyHeader1	MyHeader2	MyHeader3	MyHeader4	MyHeader5	MyHeader6	MyHeader7	MyHeader8	MyHeader9	MyHeader10
2				-		-	-		-	-
3	sdfg	gdf	654.00	1,308.00	9,879.00	3,293.00	327.00	654.00	4,939.50	654.00
4	fgh	fsd	12.00	35.00	65.00	456.00	2,345.00	354.00	3,456.00	567.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
F2	=E2/3
F3	=E3/3
G2	=C2/2
G3	=C3/2
I2	=E2/2
I3	=E3/2
J2	=D2/2
J3	=D3/2
D2	=C2*2
D3	=C3*2

 

[Cavill]

Thanks a lot guys! Machopicho your macro worked, although it copied the cells from row 3 however it copied every cell, where as I only wanted the named ones to be copied, thanks for your help though!

Jim May your macro worked perfectly how I wanted it, thank you very much! However there is one minor fault, if I use the macro once, then use it again straight after to create two new rows, an error occurs it takes me to VB and an error prompt "Run-time error '1004': No cells were found." occurs, any idea why this would be occurring? Not a huge problem if it can't be fixed.

Having 1 more problem with the button I made that has this macro assigned to it. The button moves down the page when new rows are created. I want this button to be fixed in the same spot all the time, even while new rows are created... I tried to order it "Bring to front" but that didn't work.

 

[Cavill]

I just noticed another problem. I have cells at the bottom that sum the above cells. Now with a new row, the cell that sums the cells will not add the cells in the new row that is created. I can't use absolute cell referencing because I need it to basically add all cells from row 2 all the way to the cell right above the cell that sums the above cells.
Currently I only have two cells that total the above cells, again is this possible?

 

[jim may]

Add these 2 lines (in Red), bepow

Sub MyNewRow2()
    Application.ScreenUpdating = False
    On Error Resume Next
    Rows("2:2").Insert Shift:=xlDown   'Insert New Blank Row 2
    Rows("3:3").Copy Range("A2")    'Copy dtat from Old row 2 to new row 2
    Range("A2:J2").SpecialCells(xlCellTypeConstants, 23).ClearContents     'Clear all Constants, not formulas
    Range("A2").Select
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Expand heighth of Row 1 to accomodate the size of your button (drag button so that it is totally inside row 1, not hanging over into row 2. Then with cell A2 the current cell --Freeze
the Window (that way Row 1 always appears on screen (With your button).

Expand your SUM to Include the Headings (Sum B1:B20) -- If Row 1 is text header this shouldn't be a problem.

 

[Cavill]

Wow awesome, no more errors! All problems resolved thanks a lot Jim you are a champion. My portfolio spreadsheet is looking great and functional!

 

[jim may]

Thanks for the feed-back; Glad to help.

Jim