☰

修改 Excel 宏以引用文本列表而不是硬代码

tag-icon • tag-icon microsoft-excel vba macros editing
修改 Excel 宏以引用文本列表而不是硬代码

我正在尝试获取有关我一直在使用的 Excel 宏的最后一步的帮助。我设置了一个宏，它将从两个不同工作簿的工作表中获取数据，并将其粘贴到第三个工作簿中。

第三个工作簿是 15 个文件之一，这些文件属于 15 个不同的员工，每个员工都有不同的姓名和编号。

我已经设置了宏，以便一次打开并更新 15 个工作簿中的每一个，然后保存并关闭它们，但现在我需要适应 15 个工作簿列表的变化（当员工辞职或新员工被雇用时）。

按照我编写宏的方式，我必须手动进入代码并更改对 15 名员工的姓名和 ID 号的引用。

有没有办法设置它，以便它可以从列表（保存在第四个工作簿上）中读取名称和号码，这样我就可以根据需要简单地编辑该列表，而宏可以保持不变并循环运行？我知道每次需要进行更改时我都可以使用 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

答案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

希望这是有意义的

相关内容