excel - 试图将整个工作簿粘贴到另一个工作簿的底部

Question

所以它正在工作，但它上面粘贴了 10 个空行，我不知道为什么。

Sub Stuffff()

    Dim Rng As Range
    Set Rng = ThisWorkbook.Worksheets("Sheet2").Range("A1:AY300")
    Rng.Copy

    Dim s11 As Workbook
    Set s11 = Workbooks("11 Production")
    Dim last As Long
    Dim Rngnew As Range

With s11.Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        last = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row                 
    Else
        last = 1
    End If
End With
    Set Rngnew = s11.Worksheets("Sheet1").Range("A" & last + 1)

    Rngnew.PasteSpecial
End Sub

标签： excelvba

Answer 1

也许你可以试试：

Sub Stuffff()

    Dim Rng As Range
    Set Rng = ThisWorkbook.Worksheets("Sheet2").Range("A1:AY300")
    Rng.Copy

    Dim s11 As Workbook
    Set s11 = Workbooks("11 Production")
    Dim last As Long
    Dim Rngnew As Range

With s11.Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        last = .range("A65000").end(xlup).offset(1,0).row               
    Else
        last = 1
    End If
End With
    Set Rngnew = s11.Worksheets("Sheet1").Range("A" & last)

    Rngnew.PasteSpecial
End Sub

这应该可行，除非您的工作簿中有超过 65 K 行。