Hochkommer Eintrag variieren

  • Excel

Es gibt 1 Antwort in diesem Thema. Der letzte Beitrag () ist von Cell.

    Hochkommer Eintrag variieren

    Ich habe eine Frage zu Visual Basic Das Problem ist die Bezeichnung von ("PivotTable2") Ich will das es egal ist, welche Nummer dahinter steht?
    ("PivotTable1") =("PivotTable2") =("PivotTable3") =("PivotTable4") =("PivotTable5")

    MÜSSTE in diesem Forum für niemanden ein Problem sein. Trotzdem bedanke ich mich für die Hilfe :thumbup: :thumbup:
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Sub PivotTableForm()
    2. '
    3. ' PivotTableForm Makro
    4. '
    6. '
    7. With ActiveSheet.PivotTables("PivotTable2").PivotFields("SP")
    8. .Orientation = xlColumnField
    9. .Position = 1
    10. End With
    11. ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    12. "PivotTable2").PivotFields("ProdNr"), "Summe von ProdNr", xlSum
    13. With ActiveSheet.PivotTables("PivotTable2").PivotFields("Folgeknoten")
    14. .Orientation = xlRowField
    15. .Position = 1
    16. End With
    17. ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Row Grand Total'", _
    18. xlDataAndLabel, True
    19. Range("D4").Select
    20. ActiveSheet.PivotTables("PivotTable2").GrandTotalName = "Gesamt"
    21. Range("C4").Select
    22. ActiveSheet.PivotTables("PivotTable2").PivotFields("SP").PivotItems("(blank)"). _
    23. Caption = "Frei"
    24. Range("B3").Select
    25. ActiveSheet.PivotTables("PivotTable2").CompactLayoutColumnHeader = ""
    26. Range("B4").Select
    27. ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
    28. Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    29. Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    30. With Selection.Borders(xlEdgeLeft)
    31. .LineStyle = xlContinuous
    32. .ColorIndex = 0
    33. .TintAndShade = 0
    34. .Weight = xlThin
    35. End With
    36. With Selection.Borders(xlEdgeTop)
    37. .LineStyle = xlContinuous
    38. .ColorIndex = 0
    39. .TintAndShade = 0
    40. .Weight = xlThin
    41. End With
    42. With Selection.Borders(xlEdgeBottom)
    43. .LineStyle = xlContinuous
    44. .ColorIndex = 0
    45. .TintAndShade = 0
    46. .Weight = xlThin
    47. End With
    48. With Selection.Borders(xlEdgeRight)
    49. .LineStyle = xlContinuous
    50. .ColorIndex = 0
    51. .TintAndShade = 0
    52. .Weight = xlThin
    53. End With
    54. With Selection.Borders(xlInsideVertical)
    55. .LineStyle = xlContinuous
    56. .ColorIndex = 0
    57. .TintAndShade = 0
    58. .Weight = xlThin
    59. End With
    60. With Selection.Borders(xlInsideHorizontal)
    61. .LineStyle = xlContinuous
    62. .ColorIndex = 0
    63. .TintAndShade = 0
    64. .Weight = xlThin
    65. End With
    66. With Selection.Font
    67. .Name = "Calibri"
    68. .Size = 14
    69. .Strikethrough = False
    70. .Superscript = False
    71. .Subscript = False
    72. .OutlineFont = False
    73. .Shadow = False
    74. .Underline = xlUnderlineStyleNone
    75. .ThemeColor = xlThemeColorLight1
    76. .TintAndShade = 0
    77. .ThemeFont = xlThemeFontMinor
    78. End With
    79. Columns("A:A").EntireColumn.AutoFit
    80. Cells.Select
    81. Cells.EntireColumn.AutoFit
    82. Range("C6").Select
    83. ActiveSheet.PivotTables("PivotTable2").PivotFields("Summe von ProdNr"). _
    84. Function = xlCount
    85. Range("D5").Select
    86. ActiveWindow.View = xlPageLayoutView
    87. Application.PrintCommunication = False
    88. With ActiveSheet.PageSetup
    89. .LeftHeader = ""
    90. .CenterHeader = ""
    91. .RightHeader = "&U &D"
    92. .LeftFooter = ""
    93. .CenterFooter = ""
    94. .RightFooter = ""
    95. .LeftMargin = Application.InchesToPoints(0.7)
    96. .RightMargin = Application.InchesToPoints(0.7)
    97. .TopMargin = Application.InchesToPoints(0.787401575)
    98. .BottomMargin = Application.InchesToPoints(0.787401575)
    99. .HeaderMargin = Application.InchesToPoints(0.3)
    100. .FooterMargin = Application.InchesToPoints(0.3)
    101. .Zoom = 100
    102. .PrintErrors = xlPrintErrorsDisplayed
    103. .OddAndEvenPagesHeaderFooter = False
    104. .DifferentFirstPageHeaderFooter = False
    105. .ScaleWithDocHeaderFooter = True
    106. .AlignMarginsHeaderFooter = True
    107. .EvenPage.LeftHeader.Text = ""
    108. .EvenPage.CenterHeader.Text = ""
    109. .EvenPage.RightHeader.Text = ""
    110. .EvenPage.LeftFooter.Text = ""
    111. .EvenPage.CenterFooter.Text = ""
    112. .EvenPage.RightFooter.Text = ""
    113. .FirstPage.LeftHeader.Text = ""
    114. .FirstPage.CenterHeader.Text = ""
    115. .FirstPage.RightHeader.Text = ""
    116. .FirstPage.LeftFooter.Text = ""
    117. .FirstPage.CenterFooter.Text = ""
    118. .FirstPage.RightFooter.Text = ""
    119. End With
    120. Application.PrintCommunication = True
    121. ActiveWindow.SmallScroll Down:=24
    122. Application.PrintCommunication = False
    123. With ActiveSheet.PageSetup
    124. .LeftHeader = ""
    125. .CenterHeader = ""
    126. .RightHeader = "&U &D"
    127. .LeftFooter = ""
    128. .CenterFooter = ""
    129. .RightFooter = "&N"
    130. .LeftMargin = Application.InchesToPoints(0.7)
    131. .RightMargin = Application.InchesToPoints(0.7)
    132. .TopMargin = Application.InchesToPoints(0.787401575)
    133. .BottomMargin = Application.InchesToPoints(0.787401575)
    134. .HeaderMargin = Application.InchesToPoints(0.3)
    135. .FooterMargin = Application.InchesToPoints(0.3)
    136. .Zoom = 100
    137. .PrintErrors = xlPrintErrorsDisplayed
    138. .OddAndEvenPagesHeaderFooter = False
    139. .DifferentFirstPageHeaderFooter = False
    140. .ScaleWithDocHeaderFooter = True
    141. .AlignMarginsHeaderFooter = True
    142. .EvenPage.LeftHeader.Text = ""
    143. .EvenPage.CenterHeader.Text = ""
    144. .EvenPage.RightHeader.Text = ""
    145. .EvenPage.LeftFooter.Text = ""
    146. .EvenPage.CenterFooter.Text = ""
    147. .EvenPage.RightFooter.Text = ""
    148. .FirstPage.LeftHeader.Text = ""
    149. .FirstPage.CenterHeader.Text = ""
    150. .FirstPage.RightHeader.Text = ""
    151. .FirstPage.LeftFooter.Text = ""
    152. .FirstPage.CenterFooter.Text = ""
    153. .FirstPage.RightFooter.Text = ""
    154. End With
    155. Application.PrintCommunication = True
    156. Range("E40").Select
    157. ActiveWindow.SmallScroll Down:=-39
    158. End Sub


    EDIT: Bitte Code-Tags verwenden. Lange Quelltext bitte in Spoilern verpacken. Keine Doppelpostings erstellen. ~fufu

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „fufu“ ()

    Wenn ich das richtig Sehe ist "Pivottable 1-4" die bezeichnung der jeweiligen Tabelle. Wenn die alle gleich benannt währen wüsste der Computer welche Pivottabelle du denn nun bearbeiten willst. Esseiden natürlich Pivottabelle 1 ist das gleiche wie 2 und 3 und 4... wenn das aber der Fall ist warum dann 4 Pivottabellen?

Facebook
Telefonspende