Worksheet Collation Overflow issue

Question

I'm trying to combine all data several worksheets into one, but I am getting an 'Overflow' error before it gets particularly far... Surely there is a better way of writing it to avoid this issue!

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

Use `Long` rather than `Integer` as the latter only goes up to about 32k https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long Am assuming that's the problem, though might be others. — SJR, Oct 26 '18 at 10:59

DisplayName · Accepted Answer · 2018-10-26T11:20:16.840

you are looping on new "Collated Data" sheet also

place it as the first sheet and itarate from sheet 2 on

also, you can avoid iterating through rows and copy/paste their value in one shot

finally loop through Worksheets collection, and avoid any possible Chart Sheet:

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

where has Resize been all my life? Tanks! – HotSauceCoconuts Oct 30 '18 at 09:29 — HotSauceCoconuts, Oct 30 '18 at 09:29

score 1 · Answer 2 · answered Oct 26 '18 at 11:23

My code create a new sheet named "Output" and import all the data in.

Option Explicit

    Sub test()

        Dim ws As Worksheet
        Dim wsNew As Worksheet
        Dim Lrow As Long
        Dim Excist As Boolean
        Dim SheetName As String

        SheetName = "Output"

        Excist = False

        For Each ws In ThisWorkbook.Sheets
            If ws.Name = "Output" Then
                Excist = True
                Set wsNew = ws
            End If
        Next

        If Excist = False Then
            Set wsNew = ThisWorkbook.Sheets.Add(After:= _
                ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            wsNew.Name = SheetName
        End If

        For Each ws In ThisWorkbook.Sheets
            If ws.Name <> "Output" Then
                ws.UsedRange.Copy

                Lrow = wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Row

                wsNew.Range("A" & Lrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
        Next

    End Sub

this looks good but it uses copy & paste, and word on the street is that this is slower than the value = value method. So I've been looking for ways to avoid copy & paste, but when the dimensions of the range are variable, that's where I run into trouble, because I can't say ws1.Range("A1:A10").value = ws2.Range("A1:A10").value if it is going to change everytime. Luckily I can just copy the entire row in this instance, but I'm willing to bet there is a computationally faster solution... and it has something to do with Resize — HotSauceCoconuts, Oct 30 '18 at 09:22

Worksheet Collation Overflow issue

2 Answers2