我正在其中一个 excel 文件中编写宏。我想从另一个 Excel 工作表运行它。
我的代码：
Sub Full_Automation()
Dim All_Submitted_Dates As Variant
Dim All_WorkWeek As Variant
Dim dctUnique_WorkWeek As Dictionary
Dim DateCounter As Long
Dim WorkWeekCounter As Long
Sheet1.Activate
Set dctUnique_WorkWeek = New Dictionary
With Sheet1
All_Submitted_Dates = Application.Transpose(.Range(.Range("K2"), .Cells(.Rows.Count, "K").End(xlUp)))
End With
WorkWeekCounter = 1
For DateCounter = 1 To UBound(All_Submitted_Dates)
If Not dctUnique_WorkWeek.Exists("WW" & WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter))) Then
dctUnique_WorkWeek.Add Key:="WW" & WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter)), Item:=1
Else
dctUnique_WorkWeek("WW" & WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter))) = dctUnique_WorkWeek("WW" & WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter))) + 1
End If
Next DateCounter
Worksheets.Add after:=Sheets(Sheets.Count)
Worksheets(3).Activate
Dim rowCounter As Long
Dim varKey As Variant
rowCounter = 2
For Each varKey In dctUnique_WorkWeek.Keys()
Range("A" & rowCounter).Value = varKey
Range("D" & rowCounter).Value = dctUnique_WorkWeek(varKey)
If rowCounter = 2 Then
Range("C" & rowCounter).Formula = "=B" & rowCounter
Range("E" & rowCounter).Formula = "=D" & rowCounter
Else
Range("C" & rowCounter).Formula = "=C" & (rowCounter - 1) & "+B" & rowCounter
Range("E" & rowCounter).Formula = "=E" & (rowCounter - 1) & "+D" & rowCounter
End If
rowCounter = rowCounter + 1
Next
End Sub
当我尝试逐行调试代码时，我知道每当我执行该行时，Sheet1.Activate它都会转到我的宏所在的原始 excel 文件。我将如何引用另一个工作簿的第一个工作表？
该Sheets集合是的属性Workbook对象（观察到Sheets收藏比更具包容性的worksheets集合，因为不是所有Sheets的Worksheets）。默认工作簿是，ActiveWorkbook如果您不指定任何其他内容，则会解决此问题。
您可以将工作簿分配给声明为 的变量Workbook。
Dim Wb As Workbook
Set Wb = ThisWorkbook
or
Set Wb = ActiveWorkbook
or
Set Wb = Workbooks.Open ([File name])
or
Set Wb = Workbooks.Add ([Template])
然后，您可以对指定工作簿中的任何工作表进行寻址。
Debug.Print Wb.Worksheets("Sheet1").Cells(1, 1).Value
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句