excel - 工作表排序溢出问题

Question

我正在尝试将多个工作表中的所有数据合并为一个，但在它变得特别远之前出现“溢出”错误......当然有更好的编写方法来避免这个问题!

Sub collateSheets()

Dim ws As Worksheet
Dim src As Worksheet
Dim LR As Integer
Dim LR2 As Integer

Set ws = Sheets.Add
With ws
    .Name = "Collated Data"
    .Range("1:1").Value = Sheets(2).Range("1:1").Value
End With
For i = 1 To Sheets.Count
    Sheets(i).Activate
    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
    If LR2 <> 1 Then
        For j = 2 To LR2
            LRinput = LR - 1 + j
            ws.Rows(LRinput).Value = Sheets(i).Rows(j).Value
        Next j
    End If
    LR = vbNull
    LR2 = vbNull
Next i

End Sub

Answer 1

你也在新的“整理数据”表上循环

把它放在第一张纸上，然后从第 2 页开始

此外，您可以避免遍历行并一次性复制/粘贴它们的值

最后循环 Worksheets收集，并避免任何可能的图表表:

Sub collateSheets()
    Dim ws As Worksheet
    Dim src As Worksheet
    Dim LR As Long, LR2 As Long
    Dim i As Long

    Set ws = Worksheets.Add(before:=Sheets(1)) ' place new sheet in first position
    With ws
        .Name = "Collated Data"
        .Range("1:1").value = Sheets(2).Range("1:1").value
    End With
    For i = 2 To Worksheets.Count ' loop from 2nd sheet on (thus avoiding "Collated Data")
        LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        LR2 = Sheets(i).Cells(Sheets(i).Rows.Count, 1).End(xlUp).Row
        If LR2 <> 1 Then ws.Rows(LR + 1).Resize(LR2 - 1).value = Sheets(i).Rows("2:" & LR2).value
    Next
End Sub