Results 1 to 2 of 2

Thread: Copy data from one sheet to another sheet based on combobox value

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2017
    Posts
    42

    Copy data from one sheet to another sheet based on combobox value

    Hi
    I have a textbox (txtName) and a combobox (cboDept) in a form. cboDept contents come from sheet1 column A Cell range A2:A8 .I also have additional data in cells B2:E8 in sheet1. I add names in sheet2 cell A2 and their working department in sheet2 B2,
    for example i add Smith and Financial in A2 and B2 of sheet2 .Assume Financial is in sheet1 A5 Now I want contents of B5:E5 in sheet 1 Paste into C2:F2 in sheet 2
    Code:
    Private Sub cmdAdd_Click()
        Dim LastRow As Long
        Dim ws As Worksheet
        Dim rng As Range
        Set ws = Sheets("sheet2")
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).row + 1     
        ws.Range("A" & LastRow).Value = Me.txtName.Text   
        ws.Range("A" & LastRow).Offset(0, 1).Value = Me.cboDept.Text   
           
      With cboDept_Change
      
          Select Case .Value
            Case 1:
              Set rng = Range("B2:E2")
          End Select
       End With
      Sheets("Sheet1").rng.Copy = Sheets("Sheet2").Range("C2:F2")
        
    ' Clear the form
     Me.txtName.Text = ""
     Me.txtEN.Text = ""
     Me.cboShift.Text = ""
    End Sub
    I even tried to copy after names introduction had been done with copy or Update code but couldn't. Please help to fix the code
    Thanks

  2. #2
    Member pike's Avatar
    Join Date
    Jul 2008
    Location
    Alstonville, Australia
    Posts
    52

    Re: Copy data from one sheet to another sheet based on combobox value

    Hello,
    Maybe ,,,,
    Code:
    Option Explicit
    
    Private Sub cmdAdd_Click()
        Dim LastRow As Long
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim rng As Range
        Dim rgnSearch As Range
        Dim rngFound As Range
    
        Set ws1 = Sheets("Sheet1")
        Set ws2 = Sheets("Sheet2")
    
        LastRow = ws2.Range("A" & ws2.Rows2.Count).End(xlUp).Row + 1
        ws2.Range("A" & LastRow).Value = Me.txtName.Text
        ws2.Range("A" & LastRow).Offset(0, 1).Value = Me.cboDept.Text
    
        Set rgnSearch = ws1.Range("A1", ws1.Cells(Rows2.Count, 1).End(xlUp))
    
        With rgnSearch
            Set rngFound = .Find(Me.txtName.Text, LookIn:=xlValues)
            If Not rngFound Is Nothing Then
                ws1.Range("C" & rngFound.Row & ":F" & rngFound.Row).Copy Destination:=ws2.Range("C" & LastRow)
            End If
        End With
    
        ' Clear the form
        Me.txtName.Text = ""
        Me.txtEN.Text = ""
        Me.cboShift.Text = ""
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width