Results 1 to 7 of 7

Thread: Save to pdf and excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2005
    Location
    Kuwait
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Save to pdf and excel

    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

  2. #2
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

    Have you tried:

    Code:
    ThisWorkbook.Unprotect
    
    'your code here to save copy
    
    ThisWorkbook.Protect
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Dec 2005
    Location
    Kuwait
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

    Thanks svendiamond, and where exactly does this code goes?

  4. #4
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

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

    Code:
    Sub yourMacroName()
    
    ThisWorkbook.Unprotect
    
    'the rest of your subroutine/macro/code goes here
    
    ThisWorkbook.Protect
    
    End Sub
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    New Member
    Join Date
    Dec 2005
    Location
    Kuwait
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

    Thanks a lot svendiamond. It worked

  6. #6
    New Member
    Join Date
    Dec 2005
    Location
    Kuwait
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

    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

  7. #7
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save to pdf and excel

    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
    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •