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

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
I did that and it still is not copying them down is there another code I can add?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • submittal cover log after .jpg
    submittal cover log after .jpg
    79.4 KB · Views: 1
  • submittal cover log before .jpg
    submittal cover log before .jpg
    81.1 KB · Views: 1

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
See if this works:
VBA Code:
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
 
Solution

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Works perfect thank you so much again!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,031
Messages
5,639,642
Members
417,101
Latest member
amoverton2

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
Top