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
