Hi,
Anyone can help me to understand how to add a new column in summary tab after or before copying data from multiple sheet into one?
Example: to add a new column "description" after column B at summary page.
Thank you.
Sub CombineSheet1()
Dim i As Integer
Dim Lr As Long
Dim wksDst As Worksheet
Set wksDst = Worksheets("Summary")
For i = 2 To Sheets.Count
Lr = wksDst.Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(i).Cells(1, 1).CurrentRegion.Offset(1).Copy wksDst.Cells(Lr, "A")
Next i
End Sub
Hi Chiew Yen
Try this
Sub CombineSheet1()
Dim i As Integer
Dim Lr As Long
Dim wksDst As Worksheet
Set wksDst = Worksheets("Summary")
For i = 2 To Sheets.Count
Lr = wksDst.Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(i).Cells(1, 1).CurrentRegion.Offset(1).Copy wksDst.Cells(Lr, "A")
Next i
'Insert Column
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1") = "Description"
End Sub
You can easily record a macro to insert the column.
Hope this helps.
Sunny
Thank you very much Sunny. It is really solved my problem. Now I am going to add on the highlight yellow color on the additional column add and add in the formula. Thank you!
