AW: Bestimmte Seitenanzahl über Makro Drucken
21.12.2016 19:18:21
Andi
Hallo Gerd,
auch auf die Gefahr hin, ausgeschimpft :-) zu werden- schicke ich mal die "Druckmakros" mit.
Habe deshalb mal als nicht beantwortet gekennzeichnet- >>obwohl das so nicht ganz stimmt- denn es geht
Diese sind mit dem Makrorecorder aufgezeichnet und laufen. Habe selbst versucht, diese zu bearbeiten, aber das ist mir dann doch zu kompliziert. Einiges habe ich schon gelöscht.
Möchte nur nicht alles zerstören- bei meinen VBA Kenntnissen.
Lohnt es sich, den Aufwand des "Eindampfen" zu betreiben?
Sub Drucken_Zeilen()
If Not IsNumeric(Sheets("Drucken").Range("C2").Text) Then Exit Sub
Select Case CLng(Sheets("Drucken").Range("C2").Text)
Case 1 To 21: Call Drucke_mit_Unter_1
Case 22 To 58: Call Drucke_58_mit_Unter_1
Case 59 To 99: Call Drucke_97_mit_Unter_1
Case 100 To 157: Call Drucke_137_mit_Unter_1
End Select
End Sub
Sub Loesche_Tabelle1_alt()
' Lösche Tabelle1 für neuen Druckauftrag
Sheets("Tabelle1").Select
Sheets("Tabelle1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub
Sub Drucke_mit_Unter_1()
' Einzelblatt komplett
Application.ScreenUpdating = False
Call Loesche_Tabelle1_alt
Range("A4:Q24").Select
Selection.Copy
Sheets("Einzelblatt").Select
Application.CutCopyMode = False
Sheets("Einzelblatt").Copy Before:=Sheets(1)
Sheets("Einzelblatt (2)").Select
Sheets("Einzelblatt (2)").Name = "Tabelle1"
Range("D33").Select
ActiveWindow.SmallScroll Down:=30
Range("B48:J55").Select
Selection.ClearContents
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
Range("H53").Select
Sheets("Einzelblatt").Select
ActiveWindow.SmallScroll Down:=9
Sheets("Drucken").Select
ActiveWindow.SmallScroll Down:=6
Range("B25:J32").Select
Selection.Copy
Sheets("Tabelle1").Select
Range("B49").Select
ActiveSheet.Paste
Sheets("Einzelblatt").Select
ActiveWindow.SmallScroll Down:=12
Sheets("Tabelle1").Select
ActiveWindow.SmallScroll Down:=-30
Range("B3:Q21").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveCell.FormulaR1C1 = ""
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveCell.FormulaR1C1 = ""
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 9")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
Selection.Delete
ActiveSheet.Shapes.Range(Array("Rectangle 19")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "x"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1). _
ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
.Bold = msoFalse
.NameComplexScript = "Tahoma"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Name = "Tahoma"
.Strike = msoNoStrike
End With
Selection.Delete
Sheets("Drucken").Select
ActiveWindow.SmallScroll Down:=-9
Range("A4:Q21").Select
Selection.Copy
Sheets("Einzelblatt").Select
ActiveWindow.SmallScroll Down:=-12
Sheets("Tabelle1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Bearbeiten").Select
Range("A4:Q24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveWindow.SmallScroll Down:=3
Range("A26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=21
End Sub
Sub Drucke_58_mit_Unter_1()
Call Loesche_Tabelle1_alt
Application.ScreenUpdating = False
Sheets("Zweierblatt").Copy Before:=Sheets(1)
ActiveSheet.Name = "Tabelle1"
Sheets("Drucken").Range("A10:Q21").Copy Sheets("Tabelle1").Range("A9")
Sheets("Drucken").Range("A4:L8").Copy Sheets("Tabelle1").Range("A3")
Sheets("Drucken").Range("A4:L8").Copy Sheets("Tabelle1").Range("A59")
Sheets("Bearbeiten").Range("A4:Q33").Copy
Sheets("Tabelle1").Range("A26").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Bearbeiten").Range("A34:Q62").Copy
Sheets("Tabelle1").Range("A69").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Drucken").Range("A25:Q39").Copy Sheets("Tabelle1").Range("A100")
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub Drucke_97_mit_Unter_1()
Application.ScreenUpdating = False
Call Loesche_Tabelle1_alt
Sheets("Start-Mitte-Ende3er").Copy Before:=Sheets(1)
Sheets("Start-Mitte-Ende3er (2)").Name = "Tabelle1"
Range("A9").Select
Sheets("Drucken").Select
ActiveWindow.SmallScroll Down:=-6
Range("A10:Q21").Select
Selection.Copy
Sheets("Tabelle1").Select
ActiveSheet.Paste
Range("A3").Select
Sheets("Drucken").Select
ActiveWindow.SmallScroll Down:=-6
Range("A4:L8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll Down:=1
Range("A59").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll Down:=1
Range("A111").Select
ActiveSheet.Paste
Range("A26").Select
Sheets("Bearbeiten").Select
Range("A4:Q33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=1
Range("A69").Select
ActiveWindow.SmallScroll Down:=-9
Sheets("Bearbeiten").Select
ActiveWindow.SmallScroll Down:=3
Range("Q77:Q83").Select
Range("Q83").Activate
ActiveWindow.SmallScroll Down:=-24
Range("A34:Q73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=1
Range("A121").Select
Sheets("Bearbeiten").Select
Range("A74:Q102").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=24
Range("A152").Select
Sheets("Drucken").Select
ActiveWindow.SmallScroll Down:=6
Range("A25:Q39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("Picture 13")).Select
Range("A2").Select
End Sub
Sub Drucke_137_mit_Unter_1()
Application.ScreenUpdating = False
Call Loesche_Tabelle1_alt
Sheets("Start-Mitte-Ende4er").Select
Sheets("Start-Mitte-Ende4er").Copy Before:=Sheets(1)
Sheets("Start-Mitte-Ende4er (2)").Select
Sheets("Start-Mitte-Ende4er (2)").Name = "Tabelle1"
Sheets("Drucken").Select
Range("A4:L8").Select
Selection.Copy
Sheets("Tabelle1").Select
Range("A3").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll Down:=1
Range("A59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=1
Range("A111").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=1
Range("A163").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Drucken").Select
Range("A25:Q39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveWindow.LargeScroll Down:=1
Range("A221").Select
ActiveWindow.SmallScroll Down:=-24
Range("A204").Select
ActiveSheet.Paste
Sheets("Bearbeiten").Select
ActiveWindow.SmallScroll Down:=6
Range("A113:Q141").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveWindow.SmallScroll Down:=-33
Range("A173").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-48
Range("A121").Select
Sheets("Bearbeiten").Select
ActiveWindow.SmallScroll Down:=-42
Range("A74:Q112").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=-1
Range("A69").Select
Sheets("Bearbeiten").Select
ActiveWindow.SmallScroll Down:=-24
Range("A34:Q73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll Down:=-1
Range("A26").Select
Sheets("Bearbeiten").Select
Range("A4:Q33").Select
Range("Q33").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("A9").Select
Sheets("Drucken").Select
Range("A10:Q21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Danke Andi