ich nutze bisher folgendes Makro. Nun möchte ich, dass das Makro alle Dateien inkl. aller Blätter, die sich in dem Ordner 'Makro' befinden, formatiert werden und nicht nur die Datei 'test2'.
Sub Trades_formatieren()
Dim i As Long
Workbooks.Open Filename:= _
"F:\Datenschnittstellen\ATOMIG\Master KAG\Daten\Orderimport\Koehler\Makro\test2.xls"
Application.ScreenUpdating = False
For i = 1 To 36
Columns(i).Hidden = Application.CountA(Columns(i)) = 0
Next
Application.ScreenUpdating = True
Rows("1:9").Select
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Standard"
.Size = 1
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Rows("10:10").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Rows("11:200").Select
ActiveWindow.LargeScroll Down:=-4
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Selection.RowHeight = 25
Cells.Select
Cells.EntireColumn.AutoFit
Rows("1:9").Select
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Standard"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Columns("A:AI").Select
ActiveSheet.PageSetup.PrintArea = "$A:$AI"
ActiveWindow.SmallScroll ToRight:=0
Range("E13").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$AI"
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
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Rel. transref.") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Comm.") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Fees") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Tax") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Others") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Interest") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Interest days") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Trade time") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Place of trade") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Broker ID type") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Broker ID") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Counterparty ID type") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Counterparty ID") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Tic size") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Tic value") Then
Selection.EntireColumn.Hidden = True
End If
ActiveCell.Offset(0, 1).Select
Loop
Columns("aj:aj").Select
Selection.EntireColumn.Hidden = True
End Sub
Viele Grüße
Ralf