Remove records once they are transfered

Solved/Closed
MOlie - Jul 5, 2010 at 10:26 AM
 MOlie - Jul 10, 2010 at 08:20 PM
Hi,

You help me out with the next code. what I want to do is to clear the records from the sheet query once they are transferd to the new sheet so the user does not get confuse with the last records. I know how to clear results, but this deal with the two columns is confusing me. Promise not to bother you again :)

Private Sub CommandButton1_Click()
Dim sTgtSht As String
Dim rngLastCell As Range
Dim lLastRow As Long
Dim sActiveSheet As String

sActiveSheet = ActiveSheet.Name

sTgtSht = ComboBox1.Value

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 = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(iListCount) = True Then

lLastRow = lLastRow + 1
ListBox1.Selected(iListCount) = False
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)

End If

Next iListCount

End_Sub:
Sheets(sActiveSheet).Select

End Sub

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
What is the reason of Named range "INVOICE" =TICKETS!$A$2:$A$31 ?

If you delete the rows, the range changes
0
Umm, you got a point I can change that. About my question what I want to do is to clear the records from the sheet query (the sheet where I upload all my data) once they are transfered to the sheet selected by the user trough a combobox1, The purpose is to avoid the user trasnfering the same container several times. Can you help me, please!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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      
0
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
0
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 7, 2010 at 03:42 PM
Could you repost the book and the macro and all once more
0
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
0
Actually about the last part, once the records are deleted from the tickets tab they should be saved into another worbook not a sheet , to not overload the first worbook.

Thanks again
0