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?
I need to collect data (selected range) from multiple worksheets in to new one. I used this code
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?