Excel VBA 对象不支持此属性

Question

我对VBA相当陌生。我编写了一个代码来自动将工作簿按特定条件拆分为 30 个新工作簿。它一开始确实有效，但是现在我正在增强代码以将新工作表添加到新工作簿(具有动态文件名(，并且它因不同类型的错误而中断 - Object 不支持此属性，或者文件无法使用此文件扩展名保存(以前有效(。有什么建议吗？

错误显示在以下行中：

ThisWorkbook.Worksheets("Summary").Range("A1:AK100").Select

Selection.Paste

ActiveWorkbook.SaveAs Filename:="SoC_File_SplitXXXX_RP_SoC_APAC_" & ThisWorkbook.Worksheets("Macro").Range("XFD4") & "_" & ThisWorkbook.Worksheets("Macro").Range("XFD2") & ".xlsx"- 以前工作正常...

Sub apac()
'
' apac Macro
'
'
Dim a As Workbook
Set a = ThisWorkbook
Dim aa As Worksheet
Set aa = ThisWorkbook.Worksheets.Item(8)
ThisWorkbook.Activate
ThisWorkbook.Worksheets("Data").Select
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False)
ActiveSheet.Range("A1:BB1048576").AutoFilter Field:=9, Criteria1:="APAC"
Cells.Select
Selection.Copy
Dim path As String
path = Environ$("USERPROFILE") & "Desktop" & FolderName
If Len(Dir(path, vbDirectory)) = 0 Then MkDir path
Dim myFolder$
UserName = Environ("username")
myFolder = "C:Users" & UserName & "OneDrive - XXXXXDesktopSoC_File_Split"
Workbooks.Add.Worksheets(1).Paste
Dim b As Workbook
Set b = ActiveWorkbook
Dim bb As Worksheet
Set bb = b.Sheets("Sheet1")
bb.Columns("A:ZZ").AutoFit
bb.Range("A2:BB1048576").ClearFormats
bb.Name = "Data"
Set ws = Sheets.Add(Before:=Sheets("Data"))
ActiveSheet.Name = "Summary"
ThisWorkbook.Worksheets("Summary").Range("A1:AK100").Select
Selection.Copy
b.ActiveSheet.Range("a1").Select
Selection.Paste
ActiveWorkbook.RefreshAll
ActiveWorkbook.Protect Password:="XXXX"
ActiveWorkbook.SaveAs Filename:="SoC_File_SplitXXXX_RP_SoC_APAC_" & ThisWorkbook.Worksheets("Macro").Range("XFD4") & "_" & ThisWorkbook.Worksheets("Macro").Range("XFD2") & ".xlsx"
ActiveWorkbook.Close True


End Sub

Answer 1

您无需继续选择范围即可使用它们。您可以替换它：

ThisWorkbook.Worksheets("Summary").Range("A1:AK100").Select
Selection.Copy
b.ActiveSheet.Range("a1").Select
Selection.Paste

只有这个：

ThisWorkbook.Worksheets("Summary").Range("A1:AK100").Copy Destination:=b.ActiveSheet.Range("a1")

Answer 2

在我看来，您正在添加新工作表

Set ws = Sheets.Add(Before:=Sheets("Data"))

但是然后您重命名活动表而不是那个，我认为您应该这样做

ws.Name = "Summary"

或

ws.Activate
ActiveSheet.Name = "Summary"

[编辑] 刚刚看到新创建的工作表变成了活动工作表。没关系。