Re: Makro funktioniert nicht
14.10.2002 14:11:19
Sascha
Hallo,
hier ist es:Private Sub CommandButton1_Click()
'
' DZBankGS1 Makro
' Makro am 09.10.02 von Sascha Jesse aufgezeichnet
'
'
ChDir "U:\ABLAGE\jesse\DIV"
Workbooks.OpenText FileName:="U:\ABLAGE\jesse\DIV\GS1dzbank.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(4, 1), Array(18, 1), Array(30, 1), Array(58, 9), Array(94, 1), Array(97, 1), Array _
(100, 1), Array(112, 1))
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:I").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="SA13"
Selection.AutoFilter Field:=8, Criteria1:="8884602867"
Selection.ColumnWidth = 13.14
Columns("G:G").Select
Selection.NumberFormat = "0"
Columns("E:E").ColumnWidth = 6.71
Columns("F:F").ColumnWidth = 8.29
Cells.Select
Selection.Copy
Sheets("GS1dzbank").Select
Sheets.Add
ActiveSheet.Paste
Columns("D:D").ColumnWidth = 12.86
Columns("G:G").ColumnWidth = 15.29
Columns("E:E").ColumnWidth = 5.29
Columns("F:F").ColumnWidth = 5.71
Columns("G:G").ColumnWidth = 11.71
Columns("G:G").ColumnWidth = 13.29
Columns("A:A").ColumnWidth = 7.14
Columns("B:B").ColumnWidth = 7.29
Columns("C:C").ColumnWidth = 7.43
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Kennung"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Spalte"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Zeile"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Betrag"
Range("A2").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Tabelle1!R1C1:R123C4", TableDestination:="R2C10", TableName:= _
"Pivot-Tabelle1"
ActiveSheet.PivotTables("Pivot-Tabelle1").AddFields RowFields:=Array( _
"Kennung", "Spalte", "Zeile")
ActiveSheet.PivotTables("Pivot-Tabelle1").PivotFields("Betrag").Orientation = _
xlDataField
Columns("I:I").ColumnWidth = 7.14
Columns("M:M").ColumnWidth = 13.14
Columns("M:M").Select
Selection.NumberFormat = "#,##0.00"
ActiveSheet.PivotTables("Pivot-Tabelle1").PivotSelect "Zeilengesamtergebnis", _
xlDataAndLabel
Selection.NumberFormat = "#,##0.00"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.4921259845)
.FooterMargin = Application.InchesToPoints(0.4921259845)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
Danke! Gruß Sascha