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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,285
Messages
5,635,314
Members
416,852
Latest member
kanaikls

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