Makro mit Pivot

Computer: Software & Programmierung Office Software
1

Hallo,

ich habe ein Makro inkl. Erstellung einer Pivot-Tabelle erstellt. Jetzt möchte ich das Makro auf andere Dateien anwenden und das Hin- und Herkopieren von Zellen, Erstellen von Formeln etc. geht wunderbar, aber folgende Fehlermeldung erscheint dann: „Laufzeitfehler 1004: Pivot-Table-Quelldatei ‚c:\daten…‘ kann nicht geöffnet werden.“

Wenn ich es richtig verstehe, will er die Daten der anderen Datei zum Pivot verarbeiten. Wie sage ich ihm, dass er es relativ zur aktuellen Datei machen soll?

VG,
Jan

2

ich habe ein Makro inkl. Erstellung einer Pivot-Tabelle
erstellt. Jetzt möchte ich das Makro auf andere Dateien
anwenden und das Hin- und Herkopieren von Zellen, Erstellen
von Formeln etc. geht wunderbar, aber folgende Fehlermeldung
erscheint dann: „Laufzeitfehler 1004: Pivot-Table-Quelldatei
‚c:\daten…‘ kann nicht geöffnet werden.“

Wenn ich es richtig verstehe, will er die Daten der anderen
Datei zum Pivot verarbeiten. Wie sage ich ihm, dass er es
relativ zur aktuellen Datei machen soll?

Hallo Jan,

zeige mal den Code. In welcher Codezeile falls man es nicht anhand der Fehlermeldung klar erkennen kann.

Gruß
Reinhard

3

Gerne, hier kommt der Code. Dem von Excel gelb markierten Bereich habe ich „XXX“ vorangestellt.

Sub fwws0709()
’
’ fwws0709 Makro
’

’
Rows(„1:1“).Select
Selection.Delete Shift:=xlUp
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
Rows(„2:161“).Select
Range(„BD2“).Activate
Selection.Delete Shift:=xlUp
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
Columns(„AX:AX“).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range(„AX1“).Select
ActiveCell.FormulaR1C1 = „CueVal“
Range(„AX2“).Select
ActiveCell.FormulaR1C1 = „=LEFT(RC[-1],2)“
Range(„AX2“).Select
Selection.AutoFill Destination:=Range(„AX2:AX1921“), Type:=xlFillDefault
Range(„AX2:AX1921“).Select
ActiveWindow.ScrollRow = 1888
ActiveWindow.ScrollRow = 1885
ActiveWindow.ScrollRow = 1882
ActiveWindow.ScrollRow = 1879
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1868
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1840
ActiveWindow.ScrollRow = 1778
ActiveWindow.ScrollRow = 1676
ActiveWindow.ScrollRow = 1535
ActiveWindow.ScrollRow = 1377
ActiveWindow.ScrollRow = 1142
ActiveWindow.ScrollRow = 973
ActiveWindow.ScrollRow = 806
ActiveWindow.ScrollRow = 670
ActiveWindow.ScrollRow = 549
ActiveWindow.ScrollRow = 459
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 340
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range(„AX1“).Select
Sheets.Add
XXX ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
XXX „vp102_4!R1C1:R1921C71“, Version:=xlPivotTableVersion12).CreatePivotTable _
XXX TableDestination:=„Tabelle1!R3C1“, TableName:=„PivotTable1“, _
XXX DefaultVersion:=xlPivotTableVersion12
Sheets(„Tabelle1“).Select
Cells(3, 1).Select
ActiveSheet.PivotTables(„PivotTable1“).AddDataField ActiveSheet.PivotTables( _
„PivotTable1“).PivotFields(„mask2.ACC“), „Anzahl von mask2.ACC“, xlCount
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„CueVal“)
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„SOA“)
.Orientation = xlColumnField
.Position = 2
End With
ActiveWindow.SmallScroll Down:=0
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„displayType“)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“)
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“).Orientation _
= xlHidden
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Anzahl von mask2.ACC“)
.Caption = „Mittelwert von mask2.ACC“
.Function = xlAverage
.NumberFormat = „0,000“
End With
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“)
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“).Subtotals = _
Array(False, True, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“).Subtotals = _
Array(True, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables(„PivotTable1“).PivotFields(„Qtarget.ACC“)
.Orientation = xlRowField
.Position = 2
End With
Range(„F17“).Select
ActiveSheet.PivotTables(„PivotTable1“).PivotSelect „CueVal[All;Total]“, _
xlDataAndLabel, True
ActiveWorkbook.ShowPivotTableFieldList = False
Range(„G4“).Select
ActiveWorkbook.ShowPivotTableFieldList = True
Range(„A15“).Select
ActiveCell.FormulaR1C1 = „Grouped“
Range(„A16“).Select
ActiveCell.FormulaR1C1 = „Heterogeneous“
Rows(„14:14“).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(„B15“).Select
ActiveCell.FormulaR1C1 = „SOA 130 ms“
Range(„C15“).Select
ActiveCell.FormulaR1C1 = „SOA 190 ms“
Range(„B15:C15“).Select
Selection.Copy
Range(„D15“).Select
ActiveSheet.Paste
Range(„F15“).Select
ActiveSheet.Paste
Range(„B14:C14“).Select
Application.CutCopyMode = False
Selection.Merge
Range(„D14:E14“).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range(„F14:G14“).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range(„B14:C14“).Select
ActiveCell.FormulaR1C1 = „No target“
Range(„D14:E14“).Select
ActiveCell.FormulaR1C1 = „Probe on Cue“
Range(„F14:G14“).Select
ActiveCell.FormulaR1C1 = „Probe off cue“
Range(„B16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„No“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„B17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„No“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„B16:B17“).Select
Selection.AutoFill Destination:=Range(„B16:C17“), Type:=xlFillDefault
Range(„B16:C17“).Select
Range(„C16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„No“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„C16“).Select
Selection.ClearContents
Range(„C17“).Select
Selection.ClearContents
Range(„C16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„No“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„C17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„No“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„D16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„Of“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„D17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„Of“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„E16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„Of“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„E17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„Of“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„F16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„On“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„F17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„On“","„Qtarget.ACC“",1,"„SOA“",28)"
Range(„G16“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„grouped“","„CueVal“","„On“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„G17“).Select
ActiveCell.FormulaR1C1 = _
„=GETPIVOTDATA(“„mask2.ACC“",R3C1,"„displayType“","„hetero“","„CueVal“","„On“","„Qtarget.ACC“",1,"„SOA“",88)"
Range(„B16:G17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B14:C15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„F14:G15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„D14:E15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„A16:A17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B14:C17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range(„D14:E17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range(„B16:G17“).Select
Range(„B16“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„C17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„D16“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„E16“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„E17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„F16“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„G17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„A16“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B16:G17“).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range(„C15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„E15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„G15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B14:G14“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B14:C14“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„B14:C17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Range(„D14:E17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Range(„F14:G17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Range(„B16:G17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range(„G15“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range(„A16:A17“).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range(„B19“).Select
Columns(„C:C“).ColumnWidth = 9
Columns(„B:B“).ColumnWidth = 11.43
Columns(„E:E“).ColumnWidth = 8.86
Columns(„E:E“).ColumnWidth = 11.29
Columns(„C:C“).ColumnWidth = 10.57
Columns(„F:F“).ColumnWidth = 8.14
Columns(„F:F“).ColumnWidth = 10.14
ChDir „C:_Experimente\FWWS0709_raw data behavioural“
End Sub

4

Hallo Jan,

die ganzen ScrollColumn Zeilen kannste ruaswefen bis auf die Unterste.
Unten benutzt du ChDir, aus Gründen die ich grad nicht mit Beispielen belegen kann, setze in die Zeile davor ein
ChDrive „C“
Ja, ohne Doppelpunkt.
Benutze bitte beim Code-Posten den Pre-tag, wird dann unterhalb des Eingabefensters erläutert.

Ich schaue mir mal den Code an, aber verspreche dir nicht viel davon, Pivottabellen habe ich outgesourced in die Schweiz an Superthomas bzw. an den Bodensee :smile:)

Gruß
Reinhard