Previous Topic Next Topic

Auto populating from one excel sheet to another

Closed
alenoir82 - Dec 13, 2016 at 12:19 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 20, 2016 at 11:48 AM
Hello,

Hi I am trying to figure out how I can have my employees names and department auto populate in another excel sheet once updated in the master sheet. On the master sheet I enter the employee names in columns under their corresponding department. For instance I have these departments as column headers starting from columns F2-AS2, The column headers are listed as....CLERICAL, MAINT/HKS, CRC SCAN, CRC S/R, CRC FL/PA, CRC ZONE, RES/AUDIT, RECYCLE PROJECT, M&M S/R , MNM SCAN, MNM FL/PA, MNM ZONE, VIP, TARGET.COM 331. Now I do have 2 columns in between each of theses departments listed as start date and shift for each department. Once I enter in my employee name in any of these columns I would like for the name, department, and hire date to auto populate in another sheet with in the same workbook so I wont have to enter this in manually. In the other sheet I will only have 3 columns for the Employee Name, Department, and Hire Date. Is this something that can be done? Thanks for any help!

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 20, 2016 at 11:48 AM
Hi Alenoir82,

See if the code below does the job for you: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestSheet As String

If Intersect(Target, Range("F:F,I:I,L:L,O:O,R:R")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

DestSheet = "Other Sheet"

If Target.Offset(0, 1) = vbNullString Then
    Target.Value = vbNullString
    MsgBox "Enter Start Date before Employee Name."
End If

Target.Copy Sheets(DestSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Cells(2, Target.Column).Copy Sheets(DestSheet).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Target.Offset(0, 1).Copy Sheets(DestSheet).Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

Target.Offset(1, 0).Select
End Sub

To use the code right-click the Master sheets tab and select View Code. Paste the code in the big white field.

Things you need/want to change:
  • On the forth code line you will find the column letters this code applies to. Add to this list to match your column department headers.
  • On the eighth code line you will find the name of the destination sheet, which you called Other Sheet in your query, so that is the name I went with. Change if needed.

And one more thing, the code requires you to enter the Start Date before the Employee Name. When you don't, the employee name is cleared and a message appears to remind you of this.

Best regards,
Trowa
0