Save single sheet as PDF & .xls, transfer data to master sheet, email single sheet

  • Hi,
    I’m in a real pinch…

    I am working on a project for work. I have a workbook with mutable forms (Quote, Purchase Order and several different work orders for the different departments) the sales staff have to fill out. Once they are done filling out the form they need to save the set print range of that sheet as a PDF (For the customer) and .xls (for our records, occasionally we need to make changes). These forms need to be saved as data from cells C6 (Sales staff’s name) and H9 (Customer’s name) and the date (Mar 20 2015 format). All sales staff is using the same workbook that is in a dropbox, from their own computer and need to save the files on their desktops. At the same time I need to transfer the data from cells H9, H8, C6, H6 and I42 to a master sheet where we track all forms filled out. And to top it off they need to email a copy of the sheet to our sales supervisor and the job schedule department.

    Can this be done in one big step or would it need to be broken up into parts and have a button for each action on each page?

    I have spent a LOT of time trying different VBA codes and can’t seem to get it to work (Too new at this whole VBA world). The only thing I have accomplished is saving the form as a PDF, print and clear the forms.

    Can someone please help me?

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    I am trying to save a single sheet as a .xlsx with this code....



    For some reason it is not saving it. The save as dialog opens and I can enter the name I want to save the sheet as but when I go look for it, it's not there. Where am I going wrong?

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    Quote from SaraO;742040

    For some reason it is not saving it. The save as dialog opens and I can enter the name I want to save the sheet as but when I go look for it, it's not there. Where am I going wrong?

    There is a bug in your MyRange.ExportAsFixedFormat statement. Add Option Explicit at the top of the module, outside any procedures and it will tell you the error. And the error is hidden at run-time because of the On Error Resume Next statement.


    But ExportAsFixedFormat is not used to save as xlsx. Try this instead (which saves "Sheet1" as xlsx format):

    PS - Please use CODE tags - see the forum rules for instructions.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    Thank you fro your help. I tried using your code but it is giving me a run time error 9 "Subscript out of range" on "Sheets("Sheet1").copy" line.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    It works!! Thank you so much!!


    How can I set it to save only the set print range? in this case the print range is A1:I47. I have buttons on the sheet that I don't need on the new file.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    This saves the current print area of "Sheet1" to an xlsx file. You must set the print area manually or by code before running this procedure, otherwise the warning message is displayed. As before, change "Sheet1" to the name of your sheet.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    Thank you John_W! You are a life saver. I hope you wont get frustrated with me...


    The code you gave me works like a charm. I have a cell with a dropdown option that fills other cell, those cells now show up as N/A in the new sheet. I tried to attach a screen shot but was not able to.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    Exactly what type of dropdown is it? It could be an in-cell data validation dropdown or a forms control combo box or an ActiveX combo box or something else. And exactly how does it fill other cells? Be specific.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    It is in-cell data validation dropdown, the other cells are filled with a VLOOKUP formula. Once the sales staff has selected their name in the dropdown, their information like email address, cell number and location are automatically added in the other cells with the formula.

  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    In that case we save the values only. Replace the saveRange.Copy line with:

    Code
    1. newWorkbook.Worksheets(1).Range("A1").Resize(saveRange.Rows.Count, saveRange.Columns.Count).Value = saveRange.Value
  • Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee


    Needed a different technique because the sheet contains pictures, column widths and different fonts which you want to preserve. This code copies the entire Quote sheet to a new workbook and then tidies up the result. I've removed the PrintArea If statement since it seems this isn't really required.