Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CombineallSheets macro (2003)

    I have the attached macro (with the error I am getting) that was running fine until recently. The spreadsheet I am combining has 5 sheets to combine. This never was a problem before. It seems to work when I have less sheets but I don't know why it suddenly gives me an error with 5 or more sheets. Thanks for any help you can provide.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CombineallSheets macro (2003)

    The exact cause is hard to say without the workbook to test with. Were the number of rows to be combined greater than 65536, and had a new worksheet just been added before the error? I suspect that this is happening because your code is adding a worksheet in a loop that is based on the worksheet count and on the relative positions of the worksheets. You are adding the new worksheet after the first sheet which changes the worksheet count and the relative positions of the worksheets in the workbook. After adding that worksheet, the rest of the code is not going to work properly.
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CombineallSheets macro (2003)

    The code itself is OK, but perhaps Excel runs out of resources (memory). Try adding these lines at the beginning

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    and these at the end:

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CombineallSheets macro (2003)

    I am combining worksheets that total the following:
    Worksheet one - 16384 With heading
    Worksheet two - 16384 Without a heading
    Worksheet three - 16384 Without a heading
    Worksheet four - 16384 without a heading
    Worksheet five - 4751 without a heading row

    The resulting spreadsheet has 32768 combined rows when it should have 70286 rows.

    It looks like it combines the first two sheets then it gets the error.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CombineallSheets macro (2003)

    Try something like this:

    <code>
    Public Sub CombineAllSheets()
    Dim oTgt As Worksheet, oSrc As Worksheet
    Dim strSheets() As String
    Dim I As Long, lShtCnt As Long, lLastRow As Long, lCpyRows As Long
    If Worksheets.Count <= 1 Then Exit Sub
    Set oTgt = Worksheets(1)
    lShtCnt = Worksheets.Count
    ReDim strSheets(2 To lShtCnt) As String
    For I = 2 To lShtCnt
    strSheets(I) = Worksheets(I).Name
    Next I
    For I = 2 To lShtCnt
    Set oSrc = Worksheets(strSheets(I))
    lLastRow = oTgt.Range("A1").SpecialCells(xlCellTypeLastCell). Row
    lCpyRows = oSrc.Range("A1").SpecialCells(xlCellTypeLastCell). Row
    If lLastRow + lCpyRows >= oTgt.Rows.Count Then
    Set oTgt = Worksheets.Add(after:=Worksheets(oTgt.Index))
    lLastRow = 1
    End If
    oSrc.Range(oSrc.Range("a1"), _
    oSrc.Range("a1").SpecialCells(xlCellTypeLastCell)) . _
    Copy oTgt.Cells(lLastRow + 1, 1)
    Next I
    End Sub
    </code>
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CombineallSheets macro (2003)

    Nothing was working - I was getting the same error on other macros. I rebooted and it seems that the original macro works fine now. That is one of my favorite macros and the thought of having to do what that macro does manually, sent me into a panic. Thanks for all your help and if I didn't thank you enough when you created that macro - I thank you again now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •