Consulting

Results 1 to 2 of 2

Thread: Trying to copy all sheets into one combined sheet

  1. #1
    VBAX Newbie
    Joined
    Oct 2014
    Posts
    5
    Location

    Trying to copy all sheets into one combined sheet

    Hi, In my workbook, I have many sheets with different amount of columns and different amount of rows. I just want a 'combined' sheet with the contents of the first sheet followed by the contents of the second sheet, followed by the contents of the third sheet..ect...

    I am using the following which is giving me the sheets appended in order, but its not copying all the columns :/
    Appreciate any assistance with this. Im also open to a different solution you may be willing to share

    Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
    End Sub
    Last edited by mdmackillop; 08-25-2017 at 04:10 AM. Reason: Code tags added

  2. #2
    Please use code tags around your code.

    Sub Maybe()
    Dim sh As Worksheet
    If Not [ISREF(Combined!A1)] Then
        Sheets.Add(, Sheets(Sheets.Count)).Name = "Combined"
     Else
        Sheets("Combined").UsedRange.EntireColumn.Delete
    End If
    Sheets("Sheet2").Rows(1).Copy Sheets("Combined").Cells(1, 1)
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> "Combined" Then sh.UsedRange.Offset(1).Copy Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next sh
    End Sub

Posting Permissions

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