Copy datas from other sheets and put all of them in same sheet

วันนี้ผมนำเสนอการ Copy ข้อมูลที่มีโครงสร้างตารางแบบเดียวกัน ที่อยู่ต่างชีต กัน มารวมไว้ที่เดียวกัน ในที่นี้คือ Sheet 1 ไปจนถึง Sheet 5 มารวมอยู่ในชีต temp เพื่อเตรียมทำการนำ ข้อมูลไปทำการวิเคราะห์ต่อไป รวมถึงทำการ hidden column(สามารถเปลี่ยนเป็นการ delete column ได้) ที่ไม่ต้องการไว้ด้วยในคราวเดียวกัน

I would like to present using macro script to copy datas, that they have same structure but stay in different sheets, and put them together in the same sheet. In this case, I mean copying from sheet 1 to sheet 5 and put all of them in sheet "Temp" to prepare for analysing in the next process. This also includes hiding column (or use deleting column) which we don't want in the same time. 

Sub Macro1()

'

' Macro1 Macro

'

Dim LastRow As Integer, i As Integer, erow As Integer

Dim LastCol As Integer

Sheets("temp").Select

Rows("2:" & Rows.Count).ClearContents

'---------------------------------------

Worksheets("Sheet1").Select

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

i = 1

Range(Cells(1, 1), Cells(LastRow, 21)).Select

Selection.Copy

Worksheets("Temp").Select

ActiveSheet.Cells(1, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Selection.PasteSpecial Paste:=xlPasteColumnWidths

Selection.PasteSpecial Paste:=xlPasteFormats

i = i + LastRow

'---------------------------------------

Worksheets("Sheet2").Select

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(LastRow, 21)).Select

Selection.Copy

Worksheets("Temp").Select

ActiveSheet.Cells(i, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Selection.PasteSpecial Paste:=xlPasteFormats

i = i + LastRow - 1

'---------------------------------------

Worksheets("Sheet3").Select

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(LastRow, 21)).Select

Selection.Copy

Worksheets("Temp").Select

ActiveSheet.Cells(i, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Selection.PasteSpecial Paste:=xlPasteFormats

i = i + LastRow - 1

Worksheets("temp").Select

'---------------------------------------

Worksheets("Sheet4").Select

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(LastRow, 21)).Select

Selection.Copy

Worksheets("Temp").Select

ActiveSheet.Cells(i, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Selection.PasteSpecial Paste:=xlPasteFormats

i = i + LastRow - 1

Worksheets("temp").Select

'---------------------------------------

Worksheets("Sheet5").Select

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(LastRow, 21)).Select

Selection.Copy

Worksheets("Temp").Select

ActiveSheet.Cells(i, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Selection.PasteSpecial Paste:=xlPasteFormats

i = i + LastRow - 1

Worksheets("temp").Select

'Range(Cells(1, 1), Cells(i, 21)).AutoFilter Field:=13, Criteria1:="Wait for patch"

'Application.Union(Columns("A"), Columns("C"), Columns("D"), Columns("E"), Columns("G"), Columns("H"), Columns("I"), Columns("L"), Columns("N:R"), Columns("T"), Columns("U")).Select

Worksheets("temp").Columns("A").Hidden = True

Worksheets("temp").Columns("C").Hidden = True

Worksheets("temp").Columns("D").Hidden = True

Worksheets("temp").Columns("E").Hidden = True

Worksheets("temp").Columns("G").Hidden = True

Worksheets("temp").Columns("I").Hidden = True

Worksheets("temp").Columns("L").Hidden = True

Worksheets("temp").Columns("N:R").Hidden = True

Worksheets("temp").Columns("T").Hidden = True

End Sub

Excel : Replace cells with a Header of column

Code for Replace cells with a Header of column. 

- Design start column and end column that want to replace.
- Copy cell(Header)
- Select range by F5 Go to spacial>Constants>all
  (Select cells that not blank)
- Paste value(Header)
- Loop(Do While)
------------------------------------------------------------------------------------------------------------
Sub ReplaceDataWithHeader()
Dim strCol As String
Dim lngCol As Long
lngCol = 240
 Do While lngCol < 248
    Cells(6, lngCol).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-18
    Range(Cells(27, lngCol), Cells(8096, lngCol)).Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    lngCol = lngCol + 1
 Loop
End Sub
------------------------------------------------------------------------------------------------------------