Remove records once they are transfered
Solved/Closed
Related:
- Remove records once they are transfered
- Remove wga - Download - Cleaning and optimization
- Searchbaron remove - Guide
- Sed remove line - Guide
- How to remove firefox as default browser - Guide
- Ubuntu remove password - Guide
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