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

Try replacing this line:
> Rows("3:" & lastrow).Clear

with this:
Range("A3:L" & lastrow).Clear

The former line clears entire rows from row3 down to lastrow (in Summary),
while the replacement line clears only cols A:L from row3 down to lastrow.
Hence this will preserve what you may have set up in cols to the right of col
L.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Rachel" wrote:
> 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