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