-
Mar 10th, 2017, 04:24 PM
#1
Thread Starter
Member
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
-
Mar 11th, 2017, 04:12 PM
#2
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|