Collate data
 

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

Collate data
 

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