Hi,

I am using the following macro code to copy each worksheet to a master file
worksheet. However, with this macro code, it copies the whole worksheet to
the master file. I want to put a constant column in my master file in such a
way that when i run the macro to collate the column which i placed on the
summary file will not be erased. Anyone who could help? thanks

I only want that it copies column A:L only but when I changed column below
to A:L it still copies the whole worksheet.

Sub ColateData()

Dim Dsheet As Worksheet, Ssheet As Worksheet
Dim addrow As Long, lastrow As Long
Dim source As Range, dest As Range

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Summary").Select
Set Ssheet = ThisWorkbook.Sheets("Summary")
With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow = 1 Then lastrow = 2
Rows("3:" & lastrow).Clear
End With

For Each Dsheet In Worksheets
With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
End With

If Dsheet.Name < "Summary" Then
addrow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set source = Dsheet.Range("A2:L" & addrow)
Set dest = Ssheet.Range("A" & lastrow + 1)
source.Copy dest
End If
Next

Application.ScreenUpdating = True
End Sub