Excel macro VBA add line below the previously added line and also copy format and formulas

Balloonman4343

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I've created a macro which adds a line whilst copying the format and formulas of the above line. This works fine and the code is below:

Sub New_Line()

Rows("7:7").Select'
'Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove Rows("6:6").
Select Selection.Copy Rows("7:7").
Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("G6:T6").
Select Selection.AutoFill Destination:=Range("G6:T7"), Type:=xlFillDefault Range("G7:T7").Select
ActiveWindow.ScrollColumn = 1

End Sub

However, I want to add another line under the line I have just created using the macro above and so on, so that this can be a continuous thing and to also keep a running total using the formulas from the previously added line above (hope that makes sense). I can see where the issue is in the coding above, the macro will always add the line to row 7 and copy the formula/format from row 6. I've tried adding a +1 but no success, I considered a loop but that would confuse the macro run. Can anyone please help?

Thanks in advance
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Balloonman4343

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Apologies - the coding is off. Here is the correct view of the Macro:

Sub New_Line()

Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("6:6").Select
Selection.Copy
Rows("7:7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("G6:T6").Select
Selection.AutoFill Destination:=Range("G6:T7"), Type:=xlFillDefault
Range("G7:T7").Select
ActiveWindow.ScrollColumn = 1


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,388
Messages
5,636,032
Members
416,892
Latest member
Bensch

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