Copy data from sheet 1 to multiple sheets automatically

[Paulg421]

Hi,

I am trying to copy data that is repeated from one sheet to multiple sheets. I’m using excel as a database and I don’t want to have to update names of employees on every sheet manually. I want the names and other relevant info to auto populate when it it entered on sheet 1 to the other sheets. I’m using sheet 1 as my master sheet. The other sheets are used tracking specific data about employees.

For example:

Sheet one has 5 columns
Col 1: employee name
Col 2: hire date
Col 3: employee id
Col 4: employee status
Col 5: Job class

Sheet 2 has the following columns
Col 1: employee name
Col 2: hire date

Sheet 3 has the following columns
Col 1: employee name
Col 2: employee status

How can I do this?

 

[My Aswer Is This]

I always think double clicking on a cell is best thing when you want some action to happen automatically using no buttons.

This way the script knows for sure when you want the script to run.
So with this this script when you have entered all the data you want and you are ready for the script to run
Double click on column A of the row you want copied to sheet 2 and sheet 3

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on sheets 1 Tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/20/2018  8:07:14 PM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
r = Target.Row
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim Lastrowb As Long
Lastrowb = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(r, 1).Resize(, 2).Copy Sheets(2).Cells(Lastrowa, 1)
Cells(r, 1).Copy Sheets(3).Cells(Lastrowb, 1)
Cells(r, 4).Copy Sheets(3).Cells(Lastrowb, 2)
End If
End Sub

 

[My Aswer Is This]

Now if you would like some visual effect on sheet 1 that your script ran.
Use this script exactly as explained in previous post
But with this script the cell in column A of sheet 1 will turn green after you double click on it.
Letting you know this rows data was copied over.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/20/2018  8:018:14 PM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim r As Long
r = Target.Row
Dim Lastrowa As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim Lastrowb As Long
Lastrowb = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(r, 1).Resize(, 2).Copy Sheets(2).Cells(Lastrowa, 1)
Cells(r, 1).Copy Sheets(3).Cells(Lastrowb, 1)
Cells(r, 4).Copy Sheets(3).Cells(Lastrowb, 2)
Target.Interior.ColorIndex = 4
End If
End Sub