Remove records once they are transfered
Solved/Closed
Related:
- Remove records once they are transfered
- Ubuntu remove password - Guide
- Remove recent files windows 11 - Guide
- How to remove blacklist number - Guide
- How to undo remove link on instagram bio - Instagram Forum
- How to remove call barring in keypad mobile - Android Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 7, 2010 at 12:21 AM
Jul 7, 2010 at 12:21 AM
What is the reason of Named range "INVOICE" =TICKETS!$A$2:$A$31 ?
If you delete the rows, the range changes
If you delete the rows, the range changes
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 7, 2010 at 05:47 AM
Jul 7, 2010 at 05:47 AM
I have modified two routines. Use the modified code. Leave the other routines as they were. Only these two needs to be changed
Private Sub CommandButton1_Click() Dim sTgtSht As String Dim sComboSht As String Dim sTicketSht As String Dim rngLastCell As Range Dim lLastRow As Long Dim sActiveSheet As String Dim iListCount As Long Dim lDelRow As Long Dim bDel As Boolean sComboSht = "Combo" sTicketSht = "TICKETS" sActiveSheet = ActiveSheet.Name sTgtSht = ComboBox1.Value bDel = False If (sTgtSht = "") Then MsgBox "No target sheet select." GoTo End_Sub End If On Error Resume Next Sheets(sTgtSht).Select Err.Clear On Error GoTo 0 If ActiveSheet.Name <> Sheets(sTgtSht).Name Then MsgBox "Sheet '" & sTgtSht & "' not found." GoTo End_Sub End If Set rngLastCell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious) If rngLastCell Is Nothing Then lLastRow = 1 Else lLastRow = rngLastCell.Row End If Set rngLastCell = Nothing For iListCount = 1 To ListBox1.ListCount If ListBox1.Selected(iListCount) = True Then lLastRow = lLastRow + 1 Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0) Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1) End If Next iListCount For iListCount = ListBox1.ListCount To 1 Step -1 If ListBox1.Selected(iListCount) = True Then bDel = True ListBox1.Selected(iListCount) = False lDelRow = Sheets(sComboSht).Cells(iListCount + 1, "C") Sheets(sTicketSht).Rows(lDelRow).Delete End If Next iListCount If (bDel) Then On Error Resume Next ThisWorkbook.Names("INVOICE").Delete On Error GoTo 0 ThisWorkbook.Names.Add Name:="INVOICE", RefersTo:=Worksheets(sTicketSht).Range("A2", "A31") Call ComboBox2_Change End If End_Sub: Sheets(sActiveSheet).Select End Sub Sub populateComboBox(sCombo As String) Dim rngCell As Range Dim rSource As Range Dim CW As String Dim WS As Worksheet Dim lMaxRows As Long Dim lRow As Long Dim i As Integer Set WS = Sheets("Combo") WS.AutoFilterMode = False WS.Cells.Clear Sheet9.AutoFilterMode = False Sheet9.Range("A:B").Copy WS.Range("A1").PasteSpecial Set rngCell = WS.Range("A:B").Find("*", Cells(1, 1), , , xlByRows, xlPrevious) If rngCell Is Nothing Then GoTo End_Sub lMaxRows = rngCell.Row Set WS = Sheets("Combo") With WS For lRow = 1 To lMaxRows .Cells(lRow, "C") = lRow Next lRow .Range("a:a").AutoFilter field:=1, Criteria1:="<>" & sCombo lMaxRows = .Cells(Rows.Count, "C").End(xlUp).Row End With If (lMaxRows > 1) Then WS.Rows("2:" & lMaxRows).Delete End If WS.AutoFilterMode = False Set rSource = WS.Cells(2, 3).CurrentRegion With Me.ListBox1 .ColumnCount = 2 .RowSource = rSource.Address(external:=True) For i = 1 To rSource.Columns.Count CW = CW & rSource.Columns(i).Width & ";" Next i .ColumnWidths = CW .ListIndex = -1 .Width = 320 .MultiSelect = fmMultiSelectMulti End With End_Sub: Set rngCell = Nothing End Sub
I still have a problem, All the code is working, but when the records are transfered to the specific sheets using the combo:
If you transfered one record is overwriting the heads cells and each also if you transfer more records to that sheet the records are overwriting on top of the old one, so is always overwriting the last records and is not adding them on to the sheet as a list, as it was. Any idea
Thanks
If you transfered one record is overwriting the heads cells and each also if you transfer more records to that sheet the records are overwriting on top of the old one, so is always overwriting the last records and is not adding them on to the sheet as a list, as it was. Any idea
Thanks
I forgot to tell you that beside the fact when every time the records are transfered using the transfer button, the only record transfered is the last one(example if i select 3 records from the listbox1, only the last one is transfered and is overwriting the head cells, they should start from A2 and B2.
Second If I select 3 or 4 records from the listbox1 once I press the transfer button, it deletes all the records except the first one example:
A S5R92W344
A S5R92W328
A S5R9TR341
Is deleting This, A S5R92W328, A S5R9TR341 but not the first one selected, A S5R92W344
I have been trying to resolve this issue, but so far nothing good
Second If I select 3 or 4 records from the listbox1 once I press the transfer button, it deletes all the records except the first one example:
A S5R92W344
A S5R92W328
A S5R9TR341
Is deleting This, A S5R92W328, A S5R9TR341 but not the first one selected, A S5R92W344
I have been trying to resolve this issue, but so far nothing good
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 7, 2010 at 03:42 PM
Jul 7, 2010 at 03:42 PM
Could you repost the book and the macro and all once more
Hi, firsT of all thankS for your patience, here is the file
https://authentification.site/files/23296210/Lanes1.xls
Once the records are transfered only the last one is transfered and is overwriting the header , i should start entering from from A2 and B2, not A1 and B1
And If I select 3 or 4 records from the listbox1 once I press the transfer button, it deletes all the records except the first one example:
A S5R92W344
A S5R92W328
A S5R9TR341
Is deleting This, A S5R92W328, A S5R9TR341 but not the first one, A S5R92W344
I have been thinking, I do not know how, once the records are deleted from the tickets tab, how can they also be save into another sheet ( so I can keep like a database of all the records), so it would be the values from column A and B, is also possible to write a 1 beside the each line that is transfered. maybe you will ask why? because once they are entered into the spreadsheet they also should be taken out( Iam working on that part right now) is like trying to balance, what enters the system has to go out. My idea is If I assign a 1 for the ones that enter the system I could do the same but with a minus one (-1) to balance the system. Thats my crazy idea.
And once again thank you for your help
https://authentification.site/files/23296210/Lanes1.xls
Once the records are transfered only the last one is transfered and is overwriting the header , i should start entering from from A2 and B2, not A1 and B1
And If I select 3 or 4 records from the listbox1 once I press the transfer button, it deletes all the records except the first one example:
A S5R92W344
A S5R92W328
A S5R9TR341
Is deleting This, A S5R92W328, A S5R9TR341 but not the first one, A S5R92W344
I have been thinking, I do not know how, once the records are deleted from the tickets tab, how can they also be save into another sheet ( so I can keep like a database of all the records), so it would be the values from column A and B, is also possible to write a 1 beside the each line that is transfered. maybe you will ask why? because once they are entered into the spreadsheet they also should be taken out( Iam working on that part right now) is like trying to balance, what enters the system has to go out. My idea is If I assign a 1 for the ones that enter the system I could do the same but with a minus one (-1) to balance the system. Thats my crazy idea.
And once again thank you for your help
Jul 7, 2010 at 02:57 AM