Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Transfer records to another worksheet [Solved/Closed]

Report
-
 MOlie -
Hello,

I have tried everything to move these records to a sheet called "Data" , but I wasn't able to get any good results. what I have been trying its underlined and the the records i want to transfer are in bold.

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
Dim R1 As String


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


If ListBox1.Selected(iListCount) = True Then

bDel = True
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)

With Sheets("sTgtSht")
R1 = Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)

Cells(R1).Copy Sheets("DATA").Range("A65536").End(xlUp)(2)

End With

lDelRow = Sheets(sComboSht).Cells(iListCount, "C")
Sheets(sTicketSht).Rows(lDelRow + 1).Delete



End If

Next iListCount

MsgBox lDelRow + 1 & " Containers copied To " & sTgtSht


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

Thank you



See more

4 replies

Answer 1 / 4

I solved my problem, thanks everybody. And also thanls Rizvisa
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 763
Now what? I thought it was working
Answer 3 / 4

It is working, I fixed what it need to be fixed with your help the last time. Iam not having problems with the code, What am doing it is ok i just want to add one more thing, below the bold lines iam trying to move the records to a sheet called Data but it is not working....Dont be mad on me risviza, please be nice...
Answer 4 / 4

I solved my problem. Thank you all and thanks Rizvisa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!