AW: Nachfrage
02.08.2017 16:53:52
Peter,
Moin moin Michael,
Quelltabelle 1 Spalte A = Positionsnummern(xx.xx.xx.xxxx). Spalte B = LV Kurztext (z.B.Baustelle Räumen).
Tabelle 2 werden zu jeder Position Nummer einzene Aufmass-Blätter von Position 1 bis 3085
Um nicht 3085 positionsnummer und Kurztext händeisch zu kopieren und dann separat mit pos.nummer zu speichern meine Hoffnung dieses mit einem Mackro zu erschlagen.
in einer etwas erweterten Form habe ich es mal mit einer Aufzeichnung probiert. Jedoch muss ich an dieser Stelle den Block der einzelschritte nach unten Kopieren und Anpassen.
Sub Makro6()
' Makro6 Makro
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R3C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R3C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R3C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R3C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R3C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-1.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Unprotect
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R4C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R4C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R4C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R4C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R4C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-2.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Unprotect
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R5C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R5C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R5C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R5C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R5C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-3.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Unprotect
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R6C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R6C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R6C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R6C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R6C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-4.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Unprotect
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R7C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R7C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R7C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R7C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R7C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-5.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveSheet.Unprotect
Range("AA3").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R8C1"
Range("Q13:T13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R8C3"
Range("U13:V13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R8C5"
Range("Y13:Z13").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R8C6"
Range("G14:W14").Select
ActiveCell.FormulaR1C1 = "='[POS-NR-LV-Text.xls]Sheet1'!R8C2"
Range("G15").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\peter.urban\Desktop\Katwyk\Aufmass-6.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub