I’m trying to get some help with the last step of an Excel Macro I’ve been working on. I’ve set up a Macro that will take data from sheets from two different workbooks, and paste them into a third workbook.
The third workbook is one of 15 files attributed to 15 different employees, each one identified by a different name and number.
I have set up the Macro to open and update each of the 15 workbooks one at a time, and then save and close them, but now I have a need to accommodate changes to the list of 15 (for when employees quit, or new employees get hired).
The way I’ve written the Macro, I’d have to manually go into the code and change the reference to each of the 15 employees’ names and ID numbers.
Is there a way to set it up so that it can read the name and number from a list (saved on a fourth workbook), so that I can simply edit that list as needed and the Macro can stay the same and run on a Loop? I know I could just use Replace each time I had to make a change, but it still would be easier to work from a single editable list. Thank you.
In this example below, the employee named Melvin Smith ID # 2878, is being referenced:
‘ Using the AutoFilter on the workbook called NEW DD Windows("NEW DD.xlsx").Activate ActiveSheet.Range("$H$1:$H$3055").AutoFilter Field:=8, Criteria1:=”=*Melvin Smith*”, _ Operator:=xlAnd Selection.Copy ‘ Going back to Melvin’s workbook named 2878 Windows("2878.xlsx").Activate Sheets("D dd N").Select Range("A1").Select ActiveSheet.Paste