0

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
  • I know it's generally bad form to simply say "visit this page", but the solution is a somewhat basic one in the land of coding. You need to declare and use variables to store the employees names and associated numbers. I suggest you start on the link below, give the code another shot, then come back if you're still having issues. excel-pratique.com/en/vba/variables.php – variant Sep 29 '14 at 20:54
  • How are the 15 spreadsheets named? Can you rename them to match the employee name/number? If so, when you get a new hire it could just create a new one if it's not found. If that's the case, you can pass the name or number to a variable and use that variable to target the WB – Raystafarian Sep 29 '14 at 21:17
0

The way I've done it in the past is to do exactly as you say-- I store the variables in another worksheet / book.

So let's say you create a workbook named "Employees.xlsx" that looks like this:

  | A               | B
  |-----------------|--------------------
 1| Employee        | Employee Num
 2| Melvin Smith    | 2878
 3| David Gilmour   | 1908

Then, you create two subs: one that loops through your table, and another that does the copying. We pass the employee's name and number into the second sub, and it will use those instead of the hardcoded values.

Sub CopyAllSheets()

    ' Store a reference to the workbook
    Dim Wb As Workbook
    Set Wb = Workbooks("Employees.xlsx")

    ' Start at row 2 to ignore the column headers
    Dim Counter As Long
    For Counter = 2 To Wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
        CopySingleSheet Wb.Sheets(1).Range("A" & Counter), Wb.Sheets(1).Range("B" & Counter)
    Next Counter

End Sub

Sub CopySingleSheet(EmpName As String, EmpNumber As String)

    ' Using the AutoFilter on the workbook called NEW DD
    Windows("NEW DD.xlsx").Activate
    ActiveSheet.Range("$H$1:$H$3055").AutoFilter Field:=8, Criteria1:="=*" & EmpName & "*", Operator:=xlAnd
    Selection.Copy
    ' Going back to Melvin’s workbook named 2878
    Windows(EmpNumber & ".xlsx").Activate
    Sheets("D dd N").Select
    Range("A1").Select
    ActiveSheet.Paste

End Sub

Hopefully that makes sense

  • I'm getting a compile error, and the debugger is telling me the first Counter variable is not defined. Would you happen to know an edit for this please? Thank you. – NATHAN ARNOLD Oct 3 '14 at 22:59
  • Here you go, try this. I didn't declare the Counter variable. – user184994 Oct 5 '14 at 8:51
  • Thank you so much! This worked perfectly! Very much appreciated! – NATHAN ARNOLD Oct 6 '14 at 18:51

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.