modificar macro do Excel para referenciar lista de texto em vez de código rígido

Question

Estou tentando obter ajuda com a última etapa de uma macro do Excel em que estou trabalhando. Eu configurei uma macro que pegará dados de planilhas de duas pastas de trabalho diferentes e colá-las em uma terceira pasta de trabalho.

A terceira pasta de trabalho é um dos 15 arquivos atribuídos a 15 funcionários diferentes, cada um identificado por um nome e número diferentes.

Eu configurei a Macro para abrir e atualizar cada uma das 15 pastas de trabalho uma de cada vez e depois as salve e feche, mas agora tenho a necessidade de acomodar alterações na lista de 15 (para quando os funcionários saem, ou novos funcionários são contratados).

Do jeito que eu escrevi a macro, eu teria que entrar no código manualmente e alterar a referência para cada um dos 15 nomes de funcionários e números de ID.

Existe uma maneira de configurá-lo para que ele possa ler o nome e o número de uma lista (salva em uma quarta pasta de trabalho), para que eu possa simplesmente editá-la conforme necessário e a macro continue a mesma? em um loop? Eu sei que poderia usar Substituir cada vez que tivesse que fazer uma alteração, mas ainda assim seria mais fácil trabalhar em uma única lista editável. Obrigado.

Neste exemplo abaixo, o funcionário chamado Melvin Smith ID # 2878 está sendo referenciado:

‘ 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

A maneira como fiz isso no passado é fazer exatamente o que você diz - eu armazeno as variáveis em outra planilha / livro.

Então, digamos que você crie uma pasta de trabalho chamada "Employees.xlsx" que se parece com isso:

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

Depois, você cria dois subs: um que percorre sua tabela e outro que faz a cópia. Passamos o nome e o número do funcionário para o segundo sub e ele usará esses valores em vez dos valores codificados.

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

Espero que isso faça sentido