Hi, I need to collect data (selected range) from multiple worksheets in to new one. I used this code
I want to have data from column A,B and C from Sheet1 and Sheet2 in the same columns in MergeSheet. any help?
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
This question generated 15 answers. To proceed to the answers, click here.
This thread is current as of November 22, 2015.