Sub Quaybanhtinh()
Dim bang, ver, hor, yr, kytu, kq As Variant, i, j, k As Long
Dim ws_count As Integer
Dim g As Integer
ws_count = ActiveWorkbook.Worksheet.Count
For g = 1 To ws_count
bang = .[a1].CurrentRegion
ver = .Range(.[a1], .[a1].End(4)).Value
hor = .Range(.[a1], .[a1].End(2)).Value
yr = Array(2014, 2013, 2012, 2011, 2010, 2009)
kytu = Array("Doanh Thu Thu" & ChrW(7847) & "n", _
"L" & ChrW(7907) & "i nhu" & ChrW(7853) & "n thu" & ChrW(7847) & "n t" & ChrW(7915) & " ho" & ChrW(7841) & "t " & ChrW(273) & ChrW(7897) & "ng kinh doanh", _
"T" & ChrW(7893) & "ng l" & ChrW(7907) & "i nhu" & ChrW(7853) & "n k" & ChrW(7871) & " toán tr" & ChrW(432) & ChrW(7899) & "c thu" & ChrW(7871), _
"Kh" & ChrW(7889) & "i L" & ChrW(432) & ChrW(7907) & "ng", _
"Giá Cu" & ChrW(7889) & "i K" & ChrW(7923), _
"EPS", _
"PE", _
"Giá S" & ChrW(7893) & " Sách")
For i = 2 To UBound(hor, 2)
col = Application.Match(hor(1, i), yr, 0)
If TypeName(col) = "Error" Then bang(1, i) = ""
Next
For j = 2 To UBound(ver)
rw = Application.Match(ver(j, 1), kytu, 0)
If TypeName(rw) = "Error" Then bang(j, 1) = ""
Next j
Application.ScreenUpdating = False
.[a1].CurrentRegion.Clear
.[a1].Resize(UBound(bang, 2), UBound(bang)) = Application.Transpose(bang)
.[a1].Resize(, UBound(bang)).SpecialCells(4).EntireColumn.Delete
.[a1].Resize(UBound(bang, 2)).SpecialCells(4).EntireRow.Delete
End With
Application.ScreenUpdating = True
MsBox ActiveWorkbook.Worksheet(g).Name
Next g
End Sub
