Save to pdf and excel

[jerryg72]

I have created a quotation format in excel and I have a macro in it which can save the invoice to a separate folder in.xlsx format. But is it possible for me to save the invoice to another folder in .pdf format? When the file get saved, it is saved as protected since the master file is protected. Is there any option to remove the protection on the new filed when it is saved ?


The macro that I used is:-

Sub PostToRegister()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Quotation")
Set WS2 = Worksheets("Register")
'Figure out which Row is Next
NextRow = WS2.Cells(Rows.Count, 3).End(xlUp).Row + 1

'Write Important Values to Register
WS2.Cells(NextRow, 3).Resize(1, 6).Value = Array(WS1.Range("R11"), WS1.Range("Q11"), _
WS1.Range("K10"), WS1.Range("C11"), WS1.Range("C12"), WS1.Range("W36"))

End Sub
Sub NextInvoiceNumber()
Range("Q11").Value = Range("Q11").Value + 1
Range("K10:N10, B16:Y35, L37:T37, G38:T41, W11:AD11, O13:AD13").ClearContents
End Sub


Sub SaveInvoiceWithNewFileName()
Dim NewFN As Variant
PostToRegister
ActiveSheet.Copy
NewFN = "C:\Users\Jerry\Desktop\Invoice" & Range("Q11") & "_" & Format(Now(), "yyyy-mm-dd") & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoiceNumber
End Sub

Please help. Thank you

 

[svendiamond]

Have you tried:

ThisWorkbook.Unprotect

'your code here to save copy

ThisWorkbook.Protect

 

[jerryg72]

Thanks svendiamond, and where exactly does this code goes?

 

[svendiamond]

Before and after you run your code... like:

Sub yourMacroName()

ThisWorkbook.Unprotect

'the rest of your subroutine/macro/code goes here

ThisWorkbook.Protect

End Sub

 

[jerryg72]

Thanks a lot svendiamond. It worked

 

[jerryg72]

Can I ask one more quick question please? Is there any way to deactivate/remove the formulas in the new file? Just the value is needed. Thanks again

 

[svendiamond]

Use pastespecial

Range("A1:A10").Copy
Range("B1").PasteSpecial xlPasteValues

(for example) not sure exactly what your code is doing I'd have to look closer