изменить макрос Excel, чтобы ссылаться на список текста, а не жесткий код

Question

Я пытаюсь получить некоторую помощь с последним шагом макроса Excel, над которым я работал. Я установил макрос, который будет брать данные из листов из двух разных книг и вставлять их в третью книгу.

Третья рабочая книга - это один из 15 файлов, относящихся к 15 разным сотрудникам, каждый из которых имеет свое имя и номер.

Я настроил макрос так, чтобы он открывал и обновлял каждую из 15 рабочих книг по одной, а затем сохранял и закрывал их, но теперь мне нужно учесть изменения в списке из 15 (для случаев, когда сотрудники уходят, или новых сотрудников). получить работу).

При написании макроса я должен был вручную войти в код и изменить ссылку на имена и идентификационные номера каждого из 15 сотрудников.

Есть ли способ настроить его так, чтобы он мог читать имя и номер из списка (сохраненного в четвертой книге), чтобы я мог просто редактировать этот список по мере необходимости, и макрос мог оставаться прежним и работать в цикле ? Я знаю, что я мог бы просто использовать Replace каждый раз, когда мне нужно было внести изменения, но все равно было бы легче работать из одного редактируемого списка. Спасибо.

В приведенном ниже примере ссылка на сотрудника с именем Melvin Smith ID # 2878:

‘ 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

Answer 1

То, как я это делал в прошлом, это делать точно так, как вы говорите - я храню переменные в другом листе / книге.

Допустим, вы создали рабочую книгу с именем «Employees.xlsx», которая выглядит следующим образом:

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

Затем вы создаете две подпрограммы: одна перебирает вашу таблицу, а другая - копирование. Мы передаем имя и номер сотрудника во второй подпункт, и он будет использовать их вместо жестко заданных значений.

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

Надеюсь, это имеет смысл