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

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