Set wb2 = Workbooks.Open("C:\\Users\\HP\\Desktop\\已处理新增.xlsm")报错为对象未定义

如果在 VBA 中出现“对象未定义”的错误，通常是因为代码中使用了未声明或未赋值的变量。在这段代码中，如果变量 `wb2` 在此之前没有声明或赋值，就会出现该错误。

为了解决这个问题，你可以在使用变量 `wb2` 之前声明它，并且在使用 `Workbooks.Open` 方法之前确保 Excel 应用程序处于活动状态。你可以将代码修改为以下内容：

Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Users\HP\Desktop\已处理新增.xlsm")

在这段修改后的代码中，变量 `wb2` 在使用之前被声明，并且使用了完整的文件路径，而不是使用两个反斜杠来表示一个。此外，你需要确保在使用 `Workbooks.Open` 方法之前使用 `Application.Activate` 方法将 Excel 应用程序激活。

相关问题

优化以下代码，使他在对比数据只有一行的情况下就按代码逻辑复制并粘贴那一行数据Sub CopySameDay() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim copyRange As Range Dim pasteRange As Range Dim wb As Workbook Dim folderPath As String Dim fileName As String Dim asteRange As Range Set ws = ActiveSheet lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = 2 To lastRow If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then If copyRange Is Nothing Then Set copyRange = ws.Range("A" & i - 1) End If Set pasteRange = ws.Range("A" & i) Else If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set asteRange = ws.Range("B" & i - 1) fileName = asteRange.Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A1").PasteSpecial xlPasteValues wb.Save wb.Close Set copyRange = Nothing End If End If Next i If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set asteRange = ws.Range("B" & i - 1) fileName = asteRange.Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A1").PasteSpecial xlPasteValues wb.Save wb.Close End If End Sub

Sub CopySameDay()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim copyRange As Range
Dim pasteRange As Range
Dim wb As Workbook
Dim folderPath As String
Dim fileName As String
Dim pasteSheet As Worksheet

Set ws = ActiveSheet
lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row

For i = 2 To lastRow
If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then
If copyRange Is Nothing Then
Set copyRange = ws.Range("A" & i - 1)
End If
Set pasteRange = ws.Range("A" & i)
Else
If Not copyRange Is Nothing Then
folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "\" ' Add "\" at the end of folder path

Set pasteSheet = Nothing ' Set default value for pasteSheet
On Error Resume Next ' Try to set pasteSheet as the worksheet with the same date
Set pasteSheet = wb.Sheets(Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd"))
On Error GoTo 0 ' Reset error handling

If pasteSheet Is Nothing Then ' If the worksheet with the same date doesn't exist, create one
Set wb = Workbooks.Add
wb.SaveAs folderPath & Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") & ".xlsx"
Set pasteSheet = wb.Sheets(1)
Else ' If the worksheet with the same date exists, use it
Set wb = pasteSheet.Parent
End If

copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy
pasteSheet.Range("A" & pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
wb.Save
wb.Close
Set copyRange = Nothing
End If
End If
Next i

If Not copyRange Is Nothing Then
folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "\" ' Add "\" at the end of folder path

Set pasteSheet = Nothing ' Set default value for pasteSheet
On Error Resume Next ' Try to set pasteSheet as the worksheet with the same date
Set pasteSheet = wb.Sheets(Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd"))
On Error GoTo 0 ' Reset error handling

If pasteSheet Is Nothing Then ' If the worksheet with the same date doesn't exist, create one
Set wb = Workbooks.Add
wb.SaveAs folderPath & Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") & ".xlsx"
Set pasteSheet = wb.Sheets(1)
Else ' If the worksheet with the same date exists, use it
Set wb = pasteSheet.Parent
End If

copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy
pasteSheet.Range("A" & pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
wb.Save
wb.Close
End If
End Sub

请为以下代码的每一句写上注释。Sub CopySameDay() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim copyRange As Range Dim pasteRange As Range Dim wb As Workbook Dim folderPath As String Dim fileName As String Dim sumRange As Range Dim sumValue As Double Set ws = ActiveSheet lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = 2 To lastRow If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then If copyRange Is Nothing Then Set copyRange = ws.Range("A" & i - 1) End If Set pasteRange = ws.Range("A" & i) Else If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) fileName = pasteRange.Offset(0, 1).Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") Set sumRange = wb.Sheets(wb.Sheets.Count).Range("K2:K" & (i - copyRange.Row + 2)) sumValue = Application.WorksheetFunction.Sum(sumRange) wb.Sheets(wb.Sheets.Count).Range("K2:K" & (i - copyRange.Row + 2)).NumberFormat = "0.00" copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A2") wb.Sheets(wb.Sheets.Count).Range("K" & (i - copyRange.Row + 2)).Value = sumValue ws.Rows(1).Copy wb.Sheets(wb.Sheets.Count).Range("A1") wb.Save wb.Close Set copyRange = Nothing End If End If Next i If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) fileName = pasteRange.Offset(0, 1).Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") Set sumRange = wb.Sheets(wb.Sheets.Count).Range("K2:K" & (lastRow - copyRange.Row + 3)) sumValue = Application.WorksheetFunction.Sum(sumRange) wb.Sheets(wb.Sheets.Count).Range("K2:K" & (lastRow - copyRange.Row + 3)).NumberFormat = "0.00" copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A2") wb.Sheets(wb.Sheets.Count).Range("K" & (lastRow - copyRange.Row + 3)).Value = sumValue ws.Rows(1).Copy wb.Sheets(wb.Sheets.Count).Range("A1") wb.Save wb.Close End If End Sub

注释如下：

Sub CopySameDay()

' 定义变量
Dim ws As Worksheet ' 工作表
Dim lastRow As Long ' 最后一行
Dim i As Long ' 循环计数变量
Dim copyRange As Range ' 复制区域
Dim pasteRange As Range ' 粘贴区域
Dim wb As Workbook ' 工作簿
Dim folderPath As String ' 文件夹路径
Dim fileName As String ' 文件名
Dim sumRange As Range ' 求和区域
Dim sumValue As Double ' 求和结果

' 获取当前活动工作表
Set ws = ActiveSheet

' 获取最后一行
lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row

' 循环遍历行
For i = 2 To lastRow
    ' 判断是否为同一天且同一人
    If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then
        ' 如果是，设置复制区域和粘贴区域
        If copyRange Is Nothing Then
            Set copyRange = ws.Range("A" & i - 1)
        End If
        Set pasteRange = ws.Range("A" & i)
    Else
        ' 如果不是，进行复制和粘贴操作
        If Not copyRange Is Nothing Then
            ' 获取文件夹路径和文件名
            folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))
            fileName = pasteRange.Offset(0, 1).Value & ".xlsx"
            ' 判断文件是否存在，不存在则新建，存在则打开
            If Dir(folderPath & fileName) = "" Then
                Set wb = Workbooks.Add
                wb.SaveAs folderPath & fileName
            Else
                Set wb = Workbooks.Open(folderPath & fileName)
            End If
            ' 在工作簿中添加新的工作表
            wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd")
            ' 获取求和区域和求和结果
            Set sumRange = wb.Sheets(wb.Sheets.Count).Range("K2:K" & (i - copyRange.Row + 2))
            sumValue = Application.WorksheetFunction.Sum(sumRange)
            ' 设置求和区域的数字格式
            wb.Sheets(wb.Sheets.Count).Range("K2:K" & (i - copyRange.Row + 2)).NumberFormat = "0.00"
            ' 复制数据到新的工作表中
            copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A2")
            ' 将求和结果写入新的工作表中
            wb.Sheets(wb.Sheets.Count).Range("K" & (i - copyRange.Row + 2)).Value = sumValue
            ' 将表头复制到新的工作表中
            ws.Rows(1).Copy wb.Sheets(wb.Sheets.Count).Range("A1")
            ' 保存工作簿并关闭
            wb.Save
            wb.Close
            ' 清空复制区域
            Set copyRange = Nothing
        End If
    End If
Next i

' 如果仍有复制区域，进行最后一次复制和粘贴操作
If Not copyRange Is Nothing Then
    ' 获取文件夹路径和文件名
    folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\"))
    fileName = pasteRange.Offset(0, 1).Value & ".xlsx"
    ' 判断文件是否存在，不存在则新建，存在则打开
    If Dir(folderPath & fileName) = "" Then
        Set wb = Workbooks.Add
        wb.SaveAs folderPath & fileName
    Else
        Set wb = Workbooks.Open(folderPath & fileName)
    End If
    ' 在工作簿中添加新的工作表
    wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd")
    ' 获取求和区域和求和结果
    Set sumRange = wb.Sheets(wb.Sheets.Count).Range("K2:K" & (lastRow - copyRange.Row + 3))
    sumValue = Application.WorksheetFunction.Sum(sumRange)
    ' 设置求和区域的数字格式
    wb.Sheets(wb.Sheets.Count).Range("K2:K" & (lastRow - copyRange.Row + 3)).NumberFormat = "0.00"
    ' 复制数据到新的工作表中
    copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A2")
    ' 将求和结果写入新的工作表中
    wb.Sheets(wb.Sheets.Count).Range("K" & (lastRow - copyRange.Row + 3)).Value = sumValue
    ' 将表头复制到新的工作表中
    ws.Rows(1).Copy wb.Sheets(wb.Sheets.Count).Range("A1")
    ' 保存工作簿并关闭
    wb.Save
    wb.Close
End If

End Sub