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

Copy data before TOTAL row from userform to sheet

0

Hello,

I search for way to avoid problem of copying after TOTAL(lastrow) .

the code will copy the last row from  listbox  also  I will fill combobox  to copy before TOTALrow.

what I want when try to copy data from userform to sheet and there is no empty row before TOTAL row ,then should insert new row with the same formatting and borders and fill data into new inserted row and expand range for the formulas in TOTAL row.

and if there is empty row then just fill in empty row without insert new empty row to fill it.

Private Sub CommandButton1_Click()
Dim i As Long
  With Me.ListBox1
    For i = .ListCount - 1 To 0 Step -1
      If .List(i, 0) <> "" Then
        With Sheets(1)
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = ComboBox1.Value
            .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = ListBox1.List(i, 5)
            .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = ListBox1.List(i, 6)
            .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Value = ListBox1.List(i, 7)      
        End With
        Exit For
      End If
    Next
  End With
End Sub

thanks in advanced

Answer
Discuss

Answers

0
Selected Answer

Hi again Maklil

Revision #1, 04 October 2024

In the attached, revised file, I added two variables aRw and bRw for the Total and next free rows in columns A and B respectively.

These are compared (by the new If statement) and the values from your UserForm are addded to the row in B:E.

Revision #2, 04 October 2024

If a new row needs to be added, the code adds it BEFORE the last row in B (so the range of formulae in the Totals row change) and the values are copied up to that row before the UserForm values are inserted as above.

The sum formulae in the Totals row are adjusted for the new inserted row. See changes (and comments) in bold below:.

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim aRw As Long, bRw As Long

    With Me.ListBox1

      For i = .ListCount - 1 To 0 Step -1

        If .List(i, 0) <> "" Then

          With Sheets(1)
            ' find last row in A and next row in B
            aRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            bRw = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

            If bRw = aRw Then
                ' insert row above (to adjust Totals formulae)  formatting
                .Rows(bRw - 1).Insert _
                  CopyOrigin:=xlFormatFromLeftOrAbove, _
                  Shift:=xlShiftDown
                ' copy values to inserted row (before overwrite below)
                .Rows(bRw - 1).Value = .Rows(bRw).Value
             End If
              ' write values in next row in B
              .Cells(bRw, "B").Value = ComboBox1.Value
              .Cells(bRw, "C").Value = ListBox1.List(i, 5)
              .Cells(bRw, "D").Value = ListBox1.List(i, 6)
              .Cells(bRw, "E").Value = ListBox1.List(i, 7)

          End With

          Exit For

        End If

      Next

    End With
End Sub

Hope this works well for you. if so, please remember to mark this Answer as Selected.

Discuss

Discussion

Hi John,
I'm not sure if you test the code!
but what I got copy names in column B from row2 without copy the last row from listbox  and put in row 2 for adjacant name, the amounts will copy under TOTAL row leave empty rows before TOTAL row .
MAKLIL (rep: 38) Oct 3, '24 at 11:33 am
Maklil 

I did test my code against your question. I did not alter your code that copied the last row of the Listbox BELOW the totals row (for some reason!). Your question did not explain what was expected to happen and your comment "I will fill combobox  to copy before TOTALrow" sounds like a future plan for your code.

In future please try to write your questions carefully so as not to waste my time.
John_Ru (rep: 6722) Oct 3, '24 at 5:20 pm
John
the original code will copy lastrow from listbox to sheet, but when add TOTAL row then will copy amounts from lastrow in  list box  to under TOTAL row  in  sheet froTOTAL  because there is formuals in columns C:E .
so first when try to copy  to sheet will search for empty rows before TOTAL row after that if it doesn't remain empty rows then will insert new row with the same formatting and expand range for formulas  before TOTAL row and fill data from listbox.
thanks
MAKLIL (rep: 38) Oct 3, '24 at 9:37 pm
Maklil

Please see Revision #1, 04 October 2024 to my Answer and new file.
John_Ru (rep: 6722) Oct 4, '24 at 3:02 am
Thanks gain, it doesn't expand range when calculate in TOTAL row for the formulas when add new rows .will ignore calaculation for new  ranges  where added rows as I mentioned in my question.
and expand range for the formulas in TOTAL row.
MAKLIL (rep: 38) Oct 4, '24 at 8:57 am
Maklil

Sorry but I've been doing this in a rush. I've now adjeusted the code- see Revision #2, 04 October 2024 and the new file.
John_Ru (rep: 6722) Oct 4, '24 at 4:01 pm
many thanks John.
MAKLIL (rep: 38) Oct 5, '24 at 9:00 am
Thanks for selecting my Answer, Maklil. 
John_Ru (rep: 6722) Oct 5, '24 at 2:38 pm
Add to Discussion
0

Hi MAKLIL,

Update - Oct. 5/24 Once again John was quicker to work this out than I was, nonetheless, I've attached my solution.

Here is another way to achieve what you are looking to do. This method retains the formulas in C, D, & E in the "TOTAL" row. The starting cell reference of each formula has been made absolute so it always starts adding from row 2.

Here is my revised code to add the UserForm1 data to the table on sheet CAA and to  add a new row when needed.

Updated Oct. 6/24 (as suggested)

Private Sub CommandButton1_Click()

' new code
Dim i As Long
Dim FindRow As Range
' find row with "TOTAL" in Col "A"
Set FindRow = Range("A:A").Find(What:="TOTAL", LookIn:=xlValues)
Dim aRN As Long
' row number of row with "TOTAL" in Col "A"
aRN = FindRow.Row

Dim bLR As Long   ' last used row in Col "B"
bLR = Cells(Rows.Count, "B").End(xlUp).Row

' if no name is selected - do nothing
If ComboBox1.Value = "" Then
    MsgBox "Select a name"
    Exit Sub
End If

If aRN - 1 = bLR Then
' copy row before TOTAL row, insert below itself, then delete exisiting data
    Rows(aRN - 1 & ":" & aRN - 1).Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    ' select new/inserted row
    Range("A" & aRN & ":" & "E" & aRN).Select
    ' remove existing data
    Selection.ClearContents
    'Selection.Interior.ColorIndex = xlNone
    Range("A" & aRN).Select
End If

Dim rowTB As Long

    ' copy userform data to table
        With Me.ListBox1
            For i = .ListCount - 2 To 1 Step -1
                If .List(i, 0) <> "" Then
                    With Sheets(1)
                        ' col "A" - ITEM - auto numbers each entry
                        .Cells(bLR + 1, "A").Value = bLR
                        ' col "B" - NAME
                        .Cells(bLR + 1, "B").Value = ComboBox1.Value
                        ' col "C" - EXPORT
                        .Cells(bLR + 1, "C").Value = ListBox1.List(i, 5)
                        ' col "D" - IMPORT
                        .Cells(bLR + 1, "D").Value = ListBox1.List(i, 6)
                        ' col "E" - TOTAL
                        .Cells(bLR + 1, "E").Value = ListBox1.List(i, 7)
                    End With
                End If
            Exit For
       Next
End With

' close UserForm1
Unload Me

End Sub
'

Give it a try and see if you like it. Let me know.

Cheers   :-)

Discuss

Discussion

Hi Willie
my project on userform!
I'm not sure what you try to do by your answering !
MAKLIL (rep: 38) Oct 3, '24 at 5:15 pm
Hi MAKLIL,
I've taken a closer look and I'm trying to figure out what you are looking to do. I think I understand better but don't have time right now to work on solution. Will work on it later and updat my answer. Until then - - -
Do you want to copy the userform data to the "CAA" or the "OUT" sheet? Your code refernces "Sheets(1)" which is "OUT" but the Command Button is on "CAA" which is "Sheets(2)".
Why are you not copying the "ITEM" data to col "A"?
WillieD24 (rep: 687) Oct 4, '24 at 2:33 pm
Do you want to copy the userform data to the "CAA" or the "OUT" sheet?
to CAA .
Why are you not copying the "ITEM" data to col "A"?
yes I should do by autonumbering 1,2,3  , but not copy from listbox because I copy lastrow and doesn't contain ITEM.
MAKLIL (rep: 38) Oct 5, '24 at 4:20 am
MAKLIL,

Please see my revised answer (Oct. 5) above.
WillieD24 (rep: 687) Oct 6, '24 at 11:22 am
Awesome!
in column A should autonumber 1,2,3 not from list box , becuase  the last row will be 7 in listbox then will show 7 from row2 .
so I suggest change this 
.Cells(bLR + 1, "A").Value = ListBox1.List(i, 0)

to this
.Cells(bLR + 1, "A").Value = bLR

it works for me .
many thanks for your solution.
MAKLIL (rep: 38) Oct 6, '24 at 1:52 pm
MAKLIL,

I agree with your suggestion for auto-numbering. I have adjusted my code above accordingly, as well as my file and uploaded the revised version.
(Don't know if it is possible to make more than one answer as "Selected")

Cheers   :-)
WillieD24 (rep: 687) Oct 6, '24 at 9:49 pm
Appreciated Willie!
(Don't know if it is possible to make more than one answer as "Selected")
just one answer to selecting , not more.
sorry buddy!
MAKLIL (rep: 38) Oct 7, '24 at 3:38 am
Add to Discussion


Answer the Question

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