Need A Code to generate a new row with the Macro Button

[cc11]

Can someone please help!!!

I am needing to find a code or rewrite mine so that when I press the assigned macro button it will generate a new row below the one that is above it.

This is the code that I have.

Sub NEWSUBMITTALCOVER()
'
' NEWSUBMITTALCOVER Macro
' *Create a new submittal
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim lr As Long
Dim ws As String

' Create new sheet
Sheets("Submittal Cover (0)").Select
Sheets("Submittal Cover (0)").Copy After:=Sheets(2)

' Get name of new sheet
ws = ActiveSheet.Name

' Find last row with data in column A (Totals row)
Sheets("Submittal Cover Log").Select
lr = Cells(Rows.Count, "A").End(xlUp).Row

' Insert new row
Rows(lr).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Insert formulas
Range("A" & lr).FormulaR1C1 = "='" & ws & "'!R2C1"
Range("B" & lr).FormulaR1C1 = "='" & ws & "'!R5C1"
Range("C" & lr).FormulaR1C1 = "='" & ws & "'!R3C1"
Range("D" & lr).FormulaR1C1 = "='" & ws & "'!R7C1"
Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R4C1"
Range("F" & lr).FormulaR1C1 = "='" & ws & "'!R2C3"

End Sub


@Joe4 you helped me with my last one if you could help me again I would really appreciate it! It is similar to the one we did yesterday.

 

[cc11]

I did that and it still is not copying them down is there another code I can add?

 

[Joe4]

I did that and it still is not copying them down is there another code I can add?

Are you using the Format Painter to copy the formatting (borders)?

 

[cc11]

I am not sure... The colored cells are coming down it just wont copy the boarder "allboarders" to be specific, down when i insert a new row

 

[Joe4]

The colored cells are coming down it just wont copy the boarder "allboarders" to be specific, down when i insert a new row

Since it really is a different question than the original one posted in this thread, it probably warrants its own new thread.
But I may be able to take one more quick look at it, if you post the current code you have, and before and after pictures.

 

[cc11]

Here are the before and after's and here is the code I currently have. On the before row 3 has the borders and on row 4 it does not and continues down for as many times as I click the button.

Sub NEWSUBMITTALCOVER()
'
' NEWSUBMITTALCOVER Macro
' *Create a new submittal
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim lr As Long
Dim ws As String

' Create new sheet
Sheets("Submittal Cover (0)").Select
Sheets("Submittal Cover (0)").Copy After:=Sheets(2)

' Get name of new sheet
ws = ActiveSheet.Name

' Find last row with data in column A (Totals row)
Sheets("Submittal Cover Log").Select
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

' Insert new row
Rows(lr).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Insert formulas
Range("A" & lr).FormulaR1C1 = "='" & ws & "'!R2C1"
Range("B" & lr).FormulaR1C1 = "='" & ws & "'!R5C1"
Range("C" & lr).FormulaR1C1 = "='" & ws & "'!R3C1"
Range("D" & lr).FormulaR1C1 = "='" & ws & "'!R7C1"
Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R4C1"
Range("F" & lr).FormulaR1C1 = "='" & ws & "'!R2C3"

End Sub

 

[Joe4]

See if this works:

Sub NEWSUBMITTALCOVER()
'
' NEWSUBMITTALCOVER Macro
' *Create a new submittal
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim lr As Long
Dim ws As String

' Create new sheet
Sheets("Submittal Cover (0)").Select
Sheets("Submittal Cover (0)").Copy After:=Sheets(2)

' Get name of new sheet
ws = ActiveSheet.Name

' Find last row with data in column A (Totals row)
Sheets("Submittal Cover Log").Select
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

' Insert new row
Rows(lr).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Insert formulas
Range("A" & lr).FormulaR1C1 = "='" & ws & "'!R2C1"
Range("B" & lr).FormulaR1C1 = "='" & ws & "'!R5C1"
Range("C" & lr).FormulaR1C1 = "='" & ws & "'!R3C1"
Range("D" & lr).FormulaR1C1 = "='" & ws & "'!R7C1"
Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R4C1"
Range("F" & lr).FormulaR1C1 = "='" & ws & "'!R2C3"

' Copy formatting
Range("A" & lr - 1 & ":H" & lr - 1).Copy
Range("A" & lr).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
    
End Sub

 

[cc11]

Works perfect thank you so much again!!

 

[Joe4]

You are welcome.