I have a vba which creates a new worksheets whose name is dependant on a cell value. At the same time it also created a PDF file of the same sheet. What I want it to do is also protect the newly created worksheet only. I have tried a few things to no avail.
This is what I have so far
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Add_Sheet()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
ws.Protect ""
End If
wh.Activate
Range("A1").Select
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ChDir "C:\Users\Ged\Desktop\Ged\Disability\2019-2020\Invoices"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] End Sub
[/FONT]
Just need to protect the newly created sheet only
Thanks
Ged
This is what I have so far
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Add_Sheet()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
ws.Protect ""
End If
wh.Activate
Range("A1").Select
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ChDir "C:\Users\Ged\Desktop\Ged\Disability\2019-2020\Invoices"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E9"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] End Sub
[/FONT]
Just need to protect the newly created sheet only
Thanks
Ged