Sub SoTT(Rng As Range)
With Rng
.Resize(200, 1).Clear
.Value = Evaluate("=row(R:R)")
.NumberFormat = "00"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End SubSub SoTT(Rng As Range)
With Rng
.Resize(200, 1).Clear
For Each Clls In Rng
With Clls
If .Offset(0, 1) <> "" Then
SoCap1 = SoCap1 + 1
SoCap2 = 0
.Value = Application.Roman(SoCap1)
.HorizontalAlignment = xlCenter
.Resize(1, 8).Font.Bold = True
.Resize(1, 8).Font.ColorIndex = 10
Else
SoCap2 = SoCap2 + 1
.Value = SoCap2
.Resize(1, 8).Font.Bold = False
.Resize(1, 8).Font.ColorIndex = 1
End If
End With
Next
.VerticalAlignment = xlCenter
End With
End SubBeginR = 10
EndR = [B10000].End(xlUp).Row
' Đánhsố TT cột A'
STT Range("A" & BeginR & ":A" & EndR - 1)Vậy, bắt đầu đánh số từ dòng bao nhiêu, BeginR bằng bấy nhiêu.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
BeginR = 9 'Sửa dòng bắt đầu ở dòng lệnh này'
EndR = [b200].End(xlUp).Row
SoTT Range("A" & BeginR & ":A" & EndR - 1)
DrawBorder Range("A" & BeginR & ":D" & EndR - 1) 'Nếu cột cuối không phải D thì sửa ở đây'
DrawBorder Range("A" & EndR & ":D" & EndR) 'Và đây'
End SubPrivate Sub Worksheet_Activate()
Me.PivotTables(2).PivotCache.Refresh
End Sub
Private Sub Worksheet_Activate()
Dim Pv As PivotTable
For Each Pv In Sheet2.PivotTables
Pv.PivotCache.Refresh
Next
End SubPrivate Sub Worksheet_Activate()
With Me
For i = 1 to .PivotTables.Count
.PivotTables(i).PivotCache.Refresh
Next
End With
End Sub
Dạ, đoạn code này dùng để tìm tên khi mà Mã Chủ Hàng bị gộp lại, nếu bình thường thì dư, như nó chưa tổng quát Thầy ah
Private Sub PhucHoi()
With BangPhuongAn
.AutoFilterMode = False
.Range("A9:I48, L9:O48, Q9:Q48").ClearContents
BangPivot.[X1:Z4].Copy .[A9]
End With
End SubSub TinhPhuongAn()
With Application
.EnableEvents = False: .ScreenUpdating = False
.Calculation = xlCalculationManual
On Error GoTo ExitSub
Dim MaChuHang As String, MaCH As String, PhuongAn As Range, MyRng As Range
Dim SoHangMS As Byte, SoHangPA As Byte, i As Byte, a As Byte
Dim Ham: Set Ham = .WorksheetFunction
With Range(CSDL1.[A4], CSDL1.[A65536].End(xlUp)).Resize(, 20)
If .Rows.Count = 1 Then CSDL2.[A4:T5].Name = "NguonPivot" Else .Name = "NguonPivot"
End With
With BangPivot
.PivotTables("PivotTable1").PivotCache.Refresh
.[T:V].Clear
End With
Call PhucHoi ''<--- XONG SE XOA, hoac chuyen mã cho BangPhuongAn
If BangPhuongAn.[A8] <> "" Then
With BangPhuongAn
Range(.[A9], .[A48].End(xlUp)).Resize(, 3).Copy BangPivot.[T1]
.Range("A9:I48, L9:O48, Q9:Q48").ClearContents
End With
With BangPivot
SoHangMS = .[T65536].End(xlUp).Row
For i = 1 To SoHangMS
MaCH = .Range("T" & i)
MaChuHang = .[A1]
.PivotTables("PivotTable1").PivotFields(MaChuHang).CurrentPage = MaCH
Set PhuongAn = Range(.[B5], .[B65536].End(xlUp))
SoHangPA = Ham.CountA(PhuongAn)
Set MyRng = BangPhuongAn.[A48].End(xlUp)
With PhuongAn
.Copy: MyRng.Offset(1, 16).PasteSpecial 3
.Offset(, 1).Resize(, 6).Copy: MyRng.Offset(1, 3).PasteSpecial 3
.Offset(, 7).Resize(, 4).Copy: MyRng.Offset(1, 11).PasteSpecial 3
End With
.Range("T" & i).Resize(, 3).Copy: MyRng.Offset(1).PasteSpecial 3
If SoHangPA >= 2 Then
For a = 2 To SoHangPA
BangPhuongAn.[A48].End(xlUp).Offset(1) = BangPhuongAn.[A48].End(xlUp).Value
Next
End If
Next
.PivotTables("PivotTable1").PivotFields(MaChuHang).CurrentPage = "(All)"
.[T:V].Clear
End With
End If
With BangPhuongAn
.[A8:A48].AutoFilter 1, "<>", , , False
''.[A:A].EntireColumn.Hidden = True
End With
ExitSub:
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True: .EnableEvents = True
End With
End Sub
Sub BorderSelection()
On Error Resume Next
With Selection
For i = 7 To 12
.Borders(i).LineStyle = 1
.Borders(i).Weight = 1
Next
End With
End SubSub abc()
With ActiveSheet.PivotTables(1).PivotFields("Ngày")
For i = 1 To .PivotItems.Count
With .PivotItems(i)
.Visible = (CDate(.Value) <= [H3] And CDate(.Value) >= [H2])
End With
Next
End With
End SubSub abc()
Application.ScreenUpdating = False
On Error Resume Next
With ActiveSheet.PivotTables("NHAP").PivotFields("NGAY")
For i = 1 To .PivotItems.Count
With .PivotItems(i)
.Visible = CDate(.Value) <= [H3] And CDate(.Value) >= [H2]
End With
Next
.PivotItems(ErrNo).Visible = False
End With
Application.ScreenUpdating = True
End Sub
