Need help create macro to auto populate data from cell on an employee sheet to a master sheet by first referencing store number

[cherryred3000]

Hello,
I am attempting to learn how to create a way to copy data entered on one worksheet will auto populate into another worksheet. For example, I have a list of 2000 stores with information such as location, store notes, negotiation status and columns that calculate various fees. I would like to learn how to copy data that has been inputted in to that employee's sheet will automatically update on the master sheet. The 2000 stores on the master list are divided up among the employees so the store numbers on the master list do not line up with the store numbers on the employees lists. Would I need to create a macro on the employees worksheets that somehow references the store number and then directs to the column and cell that need to be copied from the employee sheet to the master sheet?? Any help or guidance would be greatly appreciated.

 

[mrshl9898]

VLOOKUP might be something to look at.

Excel VLOOKUP Tutorial for Beginners: Step-by-Step Examples

A data search in Excel with our VLOOKUP Tutorial for Beginners. Step-by-step guidance to harness the full potential of this essential function.

www.guru99.com

 

[cherryred3000]

VLOOKUP might be something to look at.

Excel VLOOKUP Tutorial for Beginners: Step-by-Step Examples

A data search in Excel with our VLOOKUP Tutorial for Beginners. Step-by-step guidance to harness the full potential of this essential function.

www.guru99.com

I thought about that but we would have to do it for multiple columns and would we have to to do it manually every day to keep the master list updated? I was hoping to come up with a way for it to be automated so that when the store is updated by the employee it would automatically update on the master sheet as well.

 

[mrshl9898]

Sounds like a Worksheet change event might be needed for this. Make a copy of the file and place this macro in the worksheet module.

When a change is made to A1:A10000 that row is copied to the first empty row in the master (checking column A for the last row).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim master As Worksheet
    Dim LastRow As Long

    Set master = Sheets("master")  ''name of master sheet
    LastRow = master.Cells(master.Rows.Count, "A").End(xlUp).Row + 1 

    Set KeyCells = Range("A1:A10000") ''range to look for sheet changes

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        Rows(Target.Row).Copy master.Rows(LastRow)
End If
End Sub