1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
|
Sub MacTest()
Dim r, j, k As Integer
Dim SCOPE
Dim CurrentWb As Excel.Workbook
Dim SheetRCP As Excel.Worksheet
Dim SheetTCD As Excel.Worksheet
Dim Mchstr, Chv As String
Dim LastrCP, LastcCP, LastcTC, LastrTC As Long
Set CurrentWb = ThisWorkbook
Set SheetRCP = CurrentWb.Worksheets("RECAP")
LastrCP = SheetRCP.Range("E" & SheetRCP.Rows.Count).End(xlUp).Row
LastcCP = SheetRCP.Cells(2, SheetRCP.Columns.Count).End(xlToLeft).Column
For Each cell In Range(SheetRCP.Cells(3, 5), SheetRCP.Cells(5, 5))
SCOPE = cell.Value
Set SheetTCD = CurrentWb.Worksheets("TCD_" & SCOPE)
LastrTC = SheetTCD.Range("A" & SheetTCD.Rows.Count).End(xlUp).Row
LastcTC = SheetTCD.Cells(5, SheetTCD.Columns.Count).End(xlToLeft).Column
For r = 1 To Worksheets.Count
If (Worksheets(r).Name = "TCD_" & SCOPE) Then
For i = 2 To LastrCP
If (InStr(1, Trim(SheetTCD.Cells(6, 1).Value), SCOPE) > 0 And InStr(1, Trim(SheetRCP.Cells(i, 5).Value), SCOPE) > 0) Then
For k = 2 To LastcTC
If (Trim(SheetTCD.Cells(5, k).Value) = Trim(SheetRCP.Cells(2, k + 4).Value)) Then
Mchstr = "=GETPIVOTDATA("
Mchstr = Mchstr & """" & SCOPE & """"
Mchstr = Mchstr & "," & "TCD_" & SCOPE & "!R4C1,"
Mchstr = Mchstr & """" & "DT_ARRI" & """" & ","
Mchstr = Mchstr & SheetRCP.Cells(2, k + 4).Value & ")"
Chv = Mchstr
SheetRCP.Cells(i, k + 4) = Chv
End If
Next k
End If
Next i
End If
Next r
Next cell
End Sub |
Partager