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]

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!

 

[cc11]

Can anyone help me on this needing this done ASAP!!!!!




or @Joe4 could you help me again with this?

 

[Joe4]

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:

lr = Cells(Rows.Count, "A").End(xlUp).Row

to this:

lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

 

[cc11]

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

 

[cc11]

There is no total row on this workbook.

 

[cc11]

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]

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]

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]

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.