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 this situation), renames the original to Blatt 2 (Blatt 3 4 5 and so on when its the 3rd 4th 5th ...) and the copy to Vorl. Blatt+ again.

Also I want the sheet with the name "Vorl. Blatt+" not printed or saved when saving the file as pdf or sent when the file is sent in an E-mail or something, as if it is not part of the file ...
is this somehow possible?

Macros and VBA
  • The_Draco's avatar
    The_Draco
    Copper Contributor
    Aug 18, 2022

    I have done one Part of it already:

    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 Sub

    The questions now are:
    How do I prevent the "Vorl. Blatt+" sheet to be printed and saved and so on?
    How do I prevent someone to interfere while it is copying, renaming and so on?
    How do I trigger it when filling the called cells (the row that gets cleared in my code) correctly (just each time the named row on the "Vorl. Blatt+" get a cell filled) and not by itself?

    • 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?

Resources