VBA Script Insert Row and Copy Multiple Cell with Formula

Question

Hello,

I need help with VBA script to the existing VBA script to include copy cell "G" formula when insert a row. please help. Thank you.

Private Sub CommandButton1_Click()

ThisWorkbook.Worksheets("sheet1").Unprotect ("xxxxxxx")

'Insert an additional row for existing day

ActiveCell.Offset(1).EntireRow.Insert Shift:=xlShiftDown

'Insert Row

Range("B" & ActiveCell.Row & ":C" & ActiveCell.Row).Select

Application.CutCopyMode = False

Selection.Copy

'Copy Day And Date

ActiveCell.Offset(1).Select

Range("B" & ActiveCell.Row & ":C" & ActiveCell.Row).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False 

Application.CutCopyMode = False 

'Copy formulas from columns N, O, P

Range("N" & ActiveCell.Row - 1 & ":P" & ActiveCell.Row - 1).Copy

Range("N" & ActiveCell.Row & ":P" & ActiveCell.Row).PasteSpecial Paste:=xlPasteFormulas

ActiveCell.EntireRow.Cells(2).Select

ActiveCell.Value = "#" & ActiveCell.Value

ActiveCell.Offset(-1).EntireRow.Cells(15).Resize(2).FillDown

ActiveCell.EntireRow.Cells(4).Select

ThisWorkbook.Worksheets("sheet1").Protect ("xxxxxxxxxx")

End Sub

Answer 1

Hello New,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To modify the existing VBA script to include copying the formula from cell “G” when inserting a row, you can add the following lines of code after the line Application.CutCopyMode = False and before the line 'Copy formulas from columns N, O, P:

'Copy formula from column G Range("G" & ActiveCell.Row - 1). Copy Range("G" & ActiveCell.Row). PasteSpecial Paste:=xlPasteFormulas

This will copy the formula from the cell in column “G” of the row above the newly inserted row and paste it into the cell in column “G” of the newly inserted row.

Here is the modified version of your VBA script with these changes included:

Private Sub CommandButton1_Click() ThisWorkbook.Worksheets("sheet1"). Unprotect ("xxxxxxx") 'Insert an additional row for existing day ActiveCell.Offset(1). EntireRow.Insert Shift:=xlShiftDown 'Insert Row Range("B" & ActiveCell.Row & ":C" & ActiveCell.Row). Select Application.CutCopyMode = False Selection.Copy 'Copy Day And Date ActiveCell.Offset(1). Select Range("B" & ActiveCell.Row & ":C" & ActiveCell.Row). Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False

'Copy formula from column G Range("G" & ActiveCell.Row - 1). Copy Range("G" & ActiveCell.Row). PasteSpecial Paste:=xlPasteFormulas

'Copy formulas from columns N, O, P Range("N" & ActiveCell.Row - 1 & ":P" & ActiveCell.Row - 1). Copy Range("N" & ActiveCell.Row & ":P" & ActiveCell.Row). PasteSpecial Paste:=xlPasteFormulas

ActiveCell.EntireRow.Cells(2). Select ActiveCell.Value = "#" & ActiveCell.Value ActiveCell.Offset(-1). EntireRow.Cells(15). Resize(2). FillDown ActiveCell.EntireRow.Cells(4). Select

ThisWorkbook.Worksheets("sheet1"). Protect ("xxxxxxxxxx") End Sub

Please let us know if you need more help.

Best Regards, IBHADIGHI

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem.d Click Yes or No at the bottom.