Forum Discussion

The_Draco's avatar
The_Draco
Copper Contributor
Aug 18, 2022

Copy Worksheet, change original...

Well, something else: I want to get a VBA that copys a worksheet (Vorl. Blatt+) at the moment you fill one of the cells C17 till GT17 (some of them are connected but that should not be a probem in t...
Macros and VBA
The_Draco's avatar
The_Draco
Copper Contributor
Aug 18, 2022
Ok, second Part is done also:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "C17" Then Exit Sub
If Target <> "" Then
Sheets("Vorl. Blatt+").Select
Sheets("Vorl. Blatt+").Copy After:=Sheets(ActiveSheet.Range("GE12").Value)
ActiveSheet.Range("C17:GT17").ClearContents
Sheets("Vorl. Blatt+").Select
Sheets("Vorl. Blatt+").Name = "Blatt " & ActiveSheet.Range("GO12").Value
Sheets("Vorl. Blatt+ (2)").Select
Sheets("Vorl. Blatt+ (2)").Name = "Vorl. Blatt+"
Sheets("Blatt " & ActiveSheet.Range("GO12").Value).Select
End If
End Sub

Left problems are now:
How do I stop the trigger from running again if I later change something in C17 of any of the sheets (original and copys)?
And still, how do I prevent Vorl. Blatt+ from beeing printed and saved as PDF ...?
The_Draco's avatar
The_Draco
Copper Contributor
Aug 18, 2022
Very well, third problem solved:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Name <> "Vorl. Blatt+" Then Exit Sub
If Target.Address(0, 0) <> "C17" Then Exit Sub
If Target <> "" Then
Sheets("Vorl. Blatt+").Select
Sheets("Vorl. Blatt+").Copy After:=Sheets(ActiveSheet.Range("GE12").Value)
ActiveSheet.Range("C17:GT17").ClearContents
Sheets("Vorl. Blatt+").Select
Sheets("Vorl. Blatt+").Name = "Blatt " & ActiveSheet.Range("GO12").Value
Sheets("Vorl. Blatt+ (2)").Select
Sheets("Vorl. Blatt+ (2)").Name = "Vorl. Blatt+"
Sheets("Blatt " & ActiveSheet.Range("GO12").Value).Select
End If
End Sub

Now it runs just on the "Vorl. Blatt+" worksheet and not on those with another name, so still left questions:
How to prevent the worksheet named "Vorl. Blatt+" from being saved as PDF or printed?
  • The_Draco's avatar
    The_Draco
    Copper Contributor
    Aug 18, 2022
    So for the last question it may be best if I force the document to be saved as before printing, if I also can pop up a msg box that asks if the file shall be saved as "finished" or as "work in progress" version, first deleting the "Vorl. Blatt+" as it will not be needed anymore. The other version shall still have "Vorl. Blatt+" aviable but force it hidden before printing and unhiding before closing the document.
    Is that somehow possible?