Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

insert row before TOTAL row when copy from userform

0

Hi,

I  hope  finding  my  goal.

after  I  fill data  in textboxes on userform  should insert row  with  the  same  formatting before TOTAL row  and  copy  from userform and  expand the  range  in  the  formulas in TOTAL row  to include  new range  when  insert  new  row  until  the  summing  is  correct in TOTAL row  . the  textbox2,3,4  will be  numeric  values .

Private Sub CommandButton1_Click()
Dim LR As Long
Dim ws As Worksheet

Set ws = Sheets("GOODS")
With ws
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
.Rows(i).Copy
.Rows(i + 1).EntireRow.Insert
.Range("B" & LR + 1) = Date
.Range("C" & LR + 1) = TextBox1.Text
.Range("D" & LR + 1) = TextBox2.Text
.Range("E" & LR + 1) = TextBox3.Text
.Range("F" & LR + 1) = TextBox4.Text
Exit For
Next
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

End Sub

thanks

Answer
Discuss

Answers

0
Selected Answer

Hi again Abdo

The Insert method has two optional arguments, Shift (the direction to shift others cells) and CopyOrigin (where to copy formatting from, above, below, left, or right). I've used that in the attached revised file, changes to your code are in bold below, with comments to help:

Private Sub CommandButton1_Click()
Dim LR As Long
Dim ws As Worksheet

Set ws = Sheets("GOODS")
With ws
    ' determine last used cell in column B
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    'insert a row before end
    .Rows(LR - 1).Insert xlShiftDown, xlFormatFromLeftOrAbove
    ' copy values to inserted row
    .Rows(LR - 1).Value = .Rows(LR).Value
    ' copy UserForm values to last row
    .Range("B" & LR) = Date
    .Range("C" & LR) = TextBox1.Text
    .Range("D" & LR) = TextBox2.Text
    .Range("E" & LR) = TextBox3.Text
    .Range("F" & LR) = TextBox4.Text
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

End Sub

Hope this fixes things for you- if so, kindly mark this Answer as Selected.

Discuss

Discussion


I  thought  needing  to  set  variable  to  search  TOTAL  word  to  insert before  it , thanks  John 
Abdo M (rep: 16) May 23, '23 at 2:49 am
Glad that helped. I positioned the insert to avoid rewriting the formula.

Thanks for selecting my Answer, Abdo. 
John_Ru (rep: 6102) May 23, '23 at 5:32 am
Add to Discussion
0

Hello Abdo,

When I came back to post my suggested solution I noticed John had already offered one. I took a very similar approach to what John did but with a small twist.

After inserting the new row, I copied the formatting, and then copied any formulas that there might be. And at the end I updated the formulas in the "TOTAL" row. Here is the code I came up with:

Private Sub CommandButton1_Click()

Dim LR As Long ' last row (Total row) of table
Dim LRcopy As Long ' the row above the total row
Dim ws As Worksheet

Set ws = Sheets("GOODS")

With ws
LR = .Range("B" & Rows.Count).End(xlUp).Row
LRcopy = LR - 1

Rows(LR).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' copy formats to new row
Rows(LRcopy).Select
    Selection.Copy
    Rows(LR).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
' copy formulas to new row
Rows(LRcopy).Select
    Selection.Copy
    Rows(LR).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
' copy UserForm data to worksheet
.Range("B" & LR) = Date
.Range("C" & LR) = TextBox1.Text
.Range("D" & LR) = TextBox2.Text
.Range("E" & LR) = TextBox3.Text
.Range("F" & LR) = TextBox4.Text

End With
' reset textboxes to nothing (blank)
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

' update SUM formulas in TOTAL row
Range("B" & LR + 1).Select
ActiveCell.Offset(, 2).FormulaR1C1 = "=SUM(R1C:R[-1]C)"   ' column D
ActiveCell.Offset(, 3).FormulaR1C1 = "=SUM(R1C:R[-1]C)"   ' column E
ActiveCell.Offset(, 4).FormulaR1C1 = "=SUM(R1C:R[-1]C)"   ' column F

End Sub

Cheers   :-)

Discuss

Discussion

thanks,but  it  gives  error application  defined  error  in this 
Rows(LRcopy).Select
Abdo M (rep: 16) May 23, '23 at 2:50 am
Theproblem is that  the original code had 
 LR = .Range("A" & Rows.Count)...
 but that column is empty so LR becomes 1 and LR-1 is 0 (not a valid row number). I corrected that to in my Answer. 
John_Ru (rep: 6102) May 23, '23 at 5:40 am
When I reviewed this initially, I didn't notice a sample file and I only reviewed the code that was posted.
I too have corrected my answer to B.
WillieD24 (rep: 537) May 23, '23 at 10:47 am
@Willie - thanks for correcting your code. The sample file made the answer slughtly easier -(than working without ir). 
John_Ru (rep: 6102) May 23, '23 at 11:03 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login