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

cherryred3000

New Member
Joined
Jan 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

cherryred3000

New Member
Joined
Jan 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
VLOOKUP might be something to look at.

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

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
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).

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,370
Messages
5,624,294
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top