设置对工作表的引用会生成错误:对象"_Worksheet"的方法“名称"

问题描述

我的宏正在浏览一个文件夹，并选择每个Excel文件并删除第一个名为some_Accounts的选项卡，然后将粘贴数据复制到工作表名称匹配的主工作簿中.

My macro is going through a folder and picking each Excel file and deleting the first tab which is named some_Accounts and then copy pasting data to the master workbook where the worksheet names match.

在下面的代码行上获取对象'_Worksheet'的以下错误方法'Name'

Getting the following error Method 'Name' of object '_Worksheet' on the following line of code

Set wsDst = wbDst.Worksheets(wsSrc.Name)

我确保工作表名称相同.

I made sure that the worksheet names are equal.

Sub ProjectMacro()
Dim wbDst As Workbook
Dim wsDst As Worksheet
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Dim lLastRow As Long
Dim LC As Long
Dim s As Worksheet, t As String
Dim i As Long, K As Long
K = Sheets.Count

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Set wbDst = ThisWorkbook
MyPath = "C:\Users\Adam\Desktop\some files\"
strFilename = Dir(MyPath & "*.xls*", vbNormal)

Do While strFilename <> ""

    Set wbSrc = Workbooks.Open(MyPath & strFilename)

    'loop through each worksheet in the source file
    For Each wsSrc In wbSrc.Worksheets
        'Find the corresponding worksheet in the destination with the same 
        name as the source

        For i = K To 1 Step -1
            t = Sheets(i).Name
            If t = "some_Accounts" Then
                Application.DisplayAlerts = False
                Sheets(i).Delete
                Application.DisplayAlerts = True
            End If
        Next i

        Set wsDst = wbDst.Worksheets(wsSrc.Name)

        On Error GoTo 0

        If wsDst.Name = wsSrc.Name Then
            lLastRow = wsDst.UsedRange.Rows(wsDst.UsedRange.Rows.Count).Row + 1
            wsSrc.UsedRange.Copy
            wsDst.Range("A" & lLastRow).PasteSpecial xlPasteValues
        End If
    Next wsSrc

    wbSrc.Close False
    strFilename = Dir()
Loop

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

推荐答案

现在，您正在遍历 wbSrc 中的所有 Worksheets .如果 wsSrc 工作表是"some_Accounts"工作表，则在 For i = K to 1 ... End For 中删除它之后，它就不再存在，因此 wsSrc 没有 Name ，稍后将引发错误.如果要删除工作表，请之前进行此操作，以循环浏览工作簿中的所有工作表.

Right now, you are looping through all the Worksheets in wbSrc. When wsSrc is the "some_Accounts" sheet, right after you've deleted it within For i = K to 1... End For, it no longer exists, and thus wsSrc has no Name and will throw an error later on. If you're deleting a sheet, do so before you loop through all the sheets in a workbook.

但是，由于您关闭 wbSrc 而不保存更改，因此我认为您实际上不需要删除该工作表.您可以在循环播放时跳过它.

But since you are closing wbSrc without saving changes, I assume that you don't really need to delete that sheet; you can just skip it as you're looping.

这看起来像这样:

For Each wsSrc In wbSrc.Worksheets
    If wsSrc.Name <> "some_Accounts" Then
    '... copy and pasting code here
    End If
Next wsSrc

请注意，您可以将 WorksheetExists 函数合并到代码中，以确保 wbDst 中有匹配的工作表.这已经在另一个答案中提供了.

Note that you can incorporate a WorksheetExists function into your code to make sure that there is a matching sheet in wbDst. That's already been provided in another answer.

这篇关于设置对工作表的引用会生成错误:对象"_Worksheet"的方法“名称"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！