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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
So to explain this better..

The submittal cover log (check "submittal cover log pic") is where the issue is. When I click the "NEWSUBMITTALCOVER" button it is supposed to generate a new "submittal cover" tab, (refer to pic "submittal cover") which it does, and generate a new row under the previous one.

So for example if I clicked the "NEWSUBMITTALCOVER" button it would generate "Submittal Cover (1)" and then there should be a new row under row 3 on the "submittal cover log" so it would then be row 4.

However it does not do that and instead generates a new row on row 2.

My question/ issue that needs to be resolved is:
I am needing to fix my code or write a new one to make it generate a new row under the previous row on the "submittal cover log". (refer to pic "submittal cover log") Where it starts at row 3 I then would need it to go to row 4, 5, 6, etc. and so on when I click the "NEWSUBMITTALCOVER".

*If you are not @Joe4 then ignore the text below*
@Joe4 I copied all the coding we did for the PO Log you helped me with yesterday and tweaked it all to fit the needs of this Submittal Cover Log I am working on now. I am thinking it is a very small issue or part in the code that needs to be tweaked to make it function properly. Would love if you could help me again. Thanks!
 

Attachments

  • Submittal cover log.jpg
    Submittal cover log.jpg
    80.5 KB · Views: 3
  • submittal cover.jpg
    submittal cover.jpg
    97.2 KB · Views: 3

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 anyone help me on this needing this done ASAP!!!!!




or @Joe4 could you help me again with this?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
That is because in your previous one, you had a total row, and you wanted to insert the new row of data BEFORE this row. So it was being inserted BEFORE the last line of data.
On this new sheet, you want to put the new line AFTER the last row (as the last row with anything in it is row 2 - formatting does not register as data.

All you need to do is to add one to move down to the row after the last row.
So change this row:
Rich (BB code):
lr = Cells(Rows.Count, "A").End(xlUp).Row
to this:
Rich (BB code):
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
 

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

This works but is going off of row 2 instead of row 3.
 

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
There is no total row on this workbook.
 

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

So basically if you refer to the "submittal cover log" pic it is copying down from row 2 and I need it to be going off of row 3 where the green and yellow cells are.
 

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 thinking I need to change up the way it is coded to find the last row with data in column A and the insert new row macro codes. Please see the current coding I have below:

I changed the code color to red.



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
 

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
Nevermind. I had my formulas copied wrong. That was the solution, but it will not copy down the boarders when then new row is inserted down.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
but it will not copy down the boarders when then new row is inserted down.
I would recommend just adding VBA code to the end your procedure to apply (or copy) the borders you need.
You can probably get most of the code you need for that by turning on your Macro Recorder, and record yourself applying them manually.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,649
Members
417,159
Latest member
Mayozero

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