Copy Worksheet, change original...

Occasional Contributor

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?

4 Replies

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?

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