Remove records once they are transfered

[Solved/Closed]
Report
-
 MOlie -
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What is the reason of Named range "INVOICE" =TICKETS!$A$2:$A$31 ?

If you delete the rows, the range changes
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!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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      
Hi, there is no code ? :( Thanks, thanks Rizvisa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The original code. I modified it
Hi again, i had a pop pup erros screen when trying to run de code "Could not get the selected property. Invalid argument"
it is highlighting this line when errors out:

If ListBox1.Selected(iListCount) = True Then
Hi, I solved the problem, yuhuhhh!! Thanks for your help

i left the code intact, I just changed this For iListCount =ListBox1.ListCount - 1 To 0 step - 1 and it work

Thanks again Risviza you gave me the tools