R
Rachel
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
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