• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Appending multiple sheets to 1

dgk

New Member
Hi
I need a macro to do the following
Have a workbbok with about 45 sheets named “1”,”2”,”3” etc till 45 , which are filled with data till column T
There is also a sheet named “ALL-INFO” which is empty (currently)
Would like to append the data from each sheet to this sheet (ALL-INFO) and put the sheet name from where the data came next to the data in column U
Hope somebody can help me
Thanks
David
 

mohadin

Active Member
Hi
Try
Code:
Sub Allsheets()
    Dim a As Variant
    Dim sh As Worksheet
    Dim lr
    ReDim a(1 To Sheets.Count - 1)
    ReDim b(1 To Sheets.Count - 1)
    i = 1
    For Each sh In Worksheets
        If sh.Name <> "ALL-INFO" Then
            With Sheets(sh.Name)
                lr = .Cells(Rows.Count, 1).End(xlUp).Row
                a(i) = Sheets(sh.Name).Cells(1, 1).Resize(lr, 20)
                b(i) = sh.Name
                i = i + 1
            End With
        End If
    Next
    l = 1
    For i = 1 To UBound(a)
        With Sheets("ALL-INFO")
            .Range("A" & l + 1, "T" & l + UBound(a(i))) = a(i)
            .Range("U" & l + 1 & ":U" & l + UBound(a(i))) = b(i)
        End With
        l = l + UBound(a(i))
    Next
End Sub
 

dgk

New Member
hi
Sorry for the late response (due to computer issues)
you are great
your code worked flawlessly

Thanks a Million

David
 
Top