Excel: Copying data from multiple worksheets into new worksheet


Hi, I need to collect data (selected range) from multiple worksheets in to new one. I used this code

Sub AppendDataAfterLastColumn()    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range
With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
'Delete the sheet "MergeSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("MergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
'Add a worksheet with the name "MergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "MergeSheet"
'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))
    
            'Find the last Column with data on the DestSh
            Last = LastCol(DestSh)
'Fill in the column(s) that you want to copy
            Set CopyRng = sh.Range("A:C")
'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Columns.Count > DestSh.Columns.Count Then
                MsgBox "There are not enough columns in the Destsh"
                GoTo ExitTheSub
            End If
'This example copies values/formats and Column width
            CopyRng.Copy
            With DestSh.Cells(1, Last + 1)
                
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
I want to have data from column A,B and C from Sheet1 and Sheet2 in the same columns in MergeSheet. any help?


This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of November 22, 2015.


For more resources for Microsoft Excel