Tabellenblätter kopieren Formelbezüge automatisch anpassen Excel 2016

[HKindler]

Hi,

füge nach den Befehlen zum Umbenennen der Blätter jeweils noch ein Sheets(Sheets.Count - 2).Move After:=Sheets("PDF_" & .Range("Q2") - 1) ein. Die - 2 und das PDF_ natürlich anpassen!

[Kuwer]

Hallo,

Code:

Private Sub CommandButton1_Click()
  Application.ScreenUpdating = False
  With Sheets("Zert_D")
    .Range("O2").Value = .Range("O2").Value + 1
    .Range("P2").Value = .Range("P2").Value + 1
    .Range("Q2").Value = .Range("Q2").Value + 1
    Sheets(Array("PDF_1", "Zert_D", "Übertrag")).Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count - 2).Name = "PDF_" & .Range("Q2").Text
    Sheets(Sheets.Count - 1).Name = "Zert_D" & .Range("O2").Text
    Sheets(Sheets.Count - 0).Name = "Übertrag" & .Range("P2").Text
  End With
  Sheets(Sheets.Count - 2).Move Before:=Sheets("Zert_D")
  Sheets(Sheets.Count - 1).OLEObjects("CommandButton1").Delete
  Sheets(Sheets.Count - 1).Move Before:=Sheets("Übertrag")
  Application.ScreenUpdating = True
End Sub

Gruß, Uwe

[Naddus0207]

Danke Uwe 

Eine letzte Bitte habe ich noch. Auf den PDF_n Blättern ist ja ein Button zum Speichern eines PDF hinterlegt. Das verknüpfte Makro lautet ja:

Code:

Sub PDF()
'
' PDF Makro
'
'
    Range("I23:P23").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("A23:H23").Select
    Selection.Copy
    Range("I23:P23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\BZ Bad Hersfeld\BvB\BvB 2023\Zertifkate Förderzielbereiche\Berufliche Grundfertigkeiten\Berufliche Grundfertigkeiten1\" & Worksheets("PDF_1").Range("I23 ").Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, From:=1, To:=2, OpenAfterPublish:=True
    Range("I23:P23").Select
    Selection.ClearContents
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
End Sub

Das funktioniert ja dann logischerweise z.B. auf PDF_20 nicht. Ich habe den jetzt zwar 39 x kopiert und entsprechend angepasst und dann auf den entsprechenden Blättern dem jeweiligen Button zugewiesen, aber ich habe die Datei noch 3 x mal für andere Daten erstellt. Da würde ich es mir gerne ersparen, das jeweilige Makro auf dem zugehörigen Tabellenblatt zuzuweisen.

Kann man das Makro so anpassen, dass das Speichern automatisch funktioniert. Also das z.B. Max12 Mustermann12 dann beim Klicken auf dem Button in PDF_12 auch unter dem Namen "Max12 Mustermann12" gespeichert wird? Und wäre das ggf. auch möglich, dass es als pdf/a abgespeichert wird?

[HKindler]

Hi,

es würde funktionieren, wenn du vernünftig referenzieren würdest.

Also im konkreten Beispiel wäre es ausnahmsweise sogar gut, die Referenzen weg zu lassen, da dann automatisch das aktive Blatt verwendet wird, Oder noch besser: verwende einfach dieses Makro:

Code:

Sub PDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\BZ Bad Hersfeld\BvB\BvB 2023\Zertifkate Förderzielbereiche\Berufliche Grundfertigkeiten\Berufliche Grundfertigkeiten1\" & ActiveSheet.Range("A23") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, From:=1, To:=2, OpenAfterPublish:=True
End Sub

Alles andere in deinem Makro ist überflüssig. Und wenn du Spalten A:H markierst und dann einmalig "Seitenlayout" --> "Seite einrichten" --> "Druckbereich" --> "Druckbereich festlegen" auswählst, dann reicht sogar

Code:

Sub PDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\BZ Bad Hersfeld\BvB\BvB 2023\Zertifkate Förderzielbereiche\Berufliche Grundfertigkeiten\Berufliche Grundfertigkeiten1\" & Range("A23"), OpenAfterPublish:=True
End Sub

PS:
Zu deinen anderen Makros sage ich jetzt besser nichts, außre vielleicht, dass dir selbst ein Kurs "Excel: VBA für Fortgeschrittene" sicherlich gut tun würde.

[Naddus0207]

PS:
Zu deinen anderen Makros sage ich jetzt besser nichts, außre vielleicht, dass dir selbst ein Kurs "Excel: VBA für Fortgeschrittene" sicherlich gut tun würde.

Danke für die Hilfe. Ehrlich gesagt, hast du da sicher Recht. Aber meine Kenntnisse reichen nur insoweit, dass ich meist den Makrorecorder benutze und dann das aufgezeichnete Makro so lange bearbeite, bis es funktioniert. Und wenn mal was nicht so hin hauen will, suche ich mir Hilfe im Internet und baue die Sachen dann entsprechend in meine Makros ein. Wirklich selber mal ein Makro geschrieben, habe ich ehrlich gesagt noch nie.

Das mit dem pdf/a wird wohl nicht funktionieren oder? Wir müssen die PDFs über unser System an die BA übermitteln und das geht nur, wenn es im pdf/a Format gespeichert ist.

[HKindler]

Hi,

das ist wie mit dem Holzfäller, der keine Zeit hatte seine Axt zu schärfen, weil er wegen seiner stumpfen Axt bsiher viel zu wenig Bäume gefällt hatte.

Zurück zur Frage: Meines Wissen nach kann Excel kein PDF/a ausgeben. Wenn du so etwas brauchst, müsstest du über einen Druckertreiber gehen, der das kann. Da fallen mir PDF24 oder auch PDFCreator ein. Dann musst du deine Datei einfach nur über diesen Druckertreiber drucken (vorher dort das richtige Format einstellen).

[Case]

Hallo,  

in den Optionen kann man "PDF/A kompatibel" einstellen. Weiß aber nicht ab welcher Excelversion.

   

[Kuwer]

Hi Case,

ich hatte das aufgezeichnet mit und ohne Häkchen, aber die Codes waren völlig identisch.

Gruß, Uwe

[Case]

Nabend Uwe,  

du hast recht. Habe noch nichts gefunden, wie sich das per VBA steuern lässt. Den Haken muss man vor Ausführung das Makros setzen bzw. entfernen. Ist er gesetzt, kommt im Adobe Reader die Meldung "Diese Datei verlangt Konformität mit dem PDF/A-Standard und wurde schreibgeschützt geöffnet um Änderungen zu verhindern" - sonst nicht. Man kann auf "Bearbeitung aktivieren" klicken, aber dann verliert die Datei ihre PDF/A Konformität.

[HKindler]

Hi,

laut stackoverflow wird die Einstellung dieses Hakens in der Registry gespeichert. Man könnte also den Registry-Wert per VBA entsprechend setzen.