Copying data from multiple worksheets into new worksheet

[PWSY86]

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?

 

[Peter_SSs]

@Peter, if you are going to stay with this, I am going to drop off. Too many cooks spoil the broth, etc. Regards, JLG

Seems like we are resolved anyway but I'm sorry if you felt I 'intruded' on the thread. Unless there are quite a lot of contributors, I think it is often good to have a few "thinking caps" on an issue, as different aspects are often raised by different people.

Thank you very much guys!!! Finally it works... The problem was caused by hidden worksheet with commadn button in it.

Glad you got it resolved in the end.
That sounds like a strange worksheet to have - it would be hard to actually use that Command Button!

 

[JLGWhiz]

Seems like we are resolved anyway but I'm sorry if you felt I 'intruded' on the thread. Unless there are quite a lot of contributors, I think it is often good to have a few "thinking caps" on an issue, as different aspects are often raised by different people.


:

@Peter, I have no qualms with your participation. I just did not think I could contribute any more based on how the OP was responding (or not) to the different posts. But it seems to have worked out in the end.