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

SaraO · Mar 29th 2015

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?

SaraO · Mar 30th 2015

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

Code

Private Sub CommandButton2_Click()
'turn off screen updating
Dim Opendialog
Dim MyRange As Range
    Application.ScreenUpdating = False
    'open dialog and set file type
    Opendialog = Application.GetSaveAsFilename("", FileFilter:=".xlsx Files (*.xlsx), *.xlsx", _
                                               Title:="Regal Quote")
    'if no value is added for file name
    If Opendialog = False Then
        MsgBox "The operation was not successful"
        Exit Sub
    End If
    'set the named range
    With Sheet12
        Set MyRange = Sheet12.Range("A1:I81")
    End With
    'set range
    Set MyRange = Sheet12.Range("A1:I81")
    Sheet12.PageSetup.PrintArea = "A1:I81"
    
    On Error Resume Next
    MyRange.ExportAsFixedFormat Type:=xlType.xlsx, FileName:=Opendialog, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    'error handler
    On Error GoTo 0
    'clear the page breaks
    ActiveSheet.DisplayPageBreaks = False
    Application.ScreenUpdating = False
End Sub

Display More

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?

John_w · Mar 30th 2015

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

Code

Public Sub Save_Sheet_As_XLSX()


    Dim SaveAsFileName As Variant
    
    SaveAsFileName = Application.GetSaveAsFilename("", FileFilter:=".xlsx Files (*.xlsx), *.xlsx", Title:="Regal Quote")
    
    If SaveAsFileName = False Then
        MsgBox "User cancelled"
        Exit Sub
    End If
    
    'Suppress warning if file already exists
    
    Application.DisplayAlerts = False
    
    Sheets("Sheet1").Copy
    ActiveWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
    ActiveWorkbook.Close
    
    Application.DisplayAlerts = True
    
End Sub

Display More

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

SaraO · Mar 30th 2015

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.

John_w · Mar 30th 2015

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

Change "Sheet1" to the name of your sheet, for example:

Code

Sheets("My Sheet Name").Copy

SaraO · Mar 30th 2015

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.

John_w · Mar 30th 2015

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.

Code

Public Sub Save_Print_Area_As_XLSX()


    Dim SaveAsFileName As Variant
    Dim saveRange As Range
    Dim newWorkbook As Workbook
    
    With Worksheets("Sheet1")
        If .PageSetup.PrintArea <> "" Then
    
            SaveAsFileName = Application.GetSaveAsFilename("", FileFilter:=".xlsx Files (*.xlsx), *.xlsx", Title:="Regal Quote")
            
            If SaveAsFileName = False Then
                MsgBox "User cancelled"
                Exit Sub
            End If
    
            'Save print area to .xlsx file
            
            Set saveRange = .Range(.PageSetup.PrintArea)
            Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
            saveRange.Copy newWorkbook.Worksheets(1).Range("A1")
            
            'Suppress warning if file already exists
            
            Application.DisplayAlerts = False
            
            newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
            newWorkbook.Close False
            
            'Reenable Excel alerts
            
            Application.DisplayAlerts = True
    
        Else
        
            MsgBox "Print area not set on " & .Name
            
        End If
    End With
    
End Sub

Display More

SaraO · Mar 31st 2015

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.

John_w · Mar 31st 2015

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.

SaraO · Mar 31st 2015

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.

John_w · Mar 31st 2015

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

newWorkbook.Worksheets(1).Range("A1").Resize(saveRange.Rows.Count, saveRange.Columns.Count).Value = saveRange.Value

SaraO · Mar 31st 2015

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

forum.ozgrid.com/index.php?attachment/64584/

That doesn't seem to do the trick.

I have attached an example of the sheet I am working with. (I hope I attached it right)

I would like to keep the same page layout so that the new copy looks like the original one.

John_w · Mar 31st 2015

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.

Code

Public Sub Save_Quote()


    Dim SaveAsFileName As Variant
    Dim newWorkbook As Workbook
    
    SaveAsFileName = Application.GetSaveAsFilename("", FileFilter:=".xlsx Files (*.xlsx), *.xlsx", Title:="Regal Quote")
    
    If SaveAsFileName = False Then
        MsgBox "User cancelled"
        Exit Sub
    End If


    'Create new workbook with 1 empty sheet
    
    Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
    
    'Copy entire Quote sheet to new workbook - this becomes the first of 2 sheets in new workbook
    
    ThisWorkbook.Worksheets("Quote").Copy Before:=newWorkbook.Worksheets(1)
    
    'Tidy up copied Quote sheet
    
    With newWorkbook.Worksheets(1)
    
        'Convert formulas to their values
        
        .Range("F2:F5").Value = .Range("F2:F5").Value
        .Range("H8:H10").Value = .Range("H8:H10").Value
        .Range("I37:I41").Value = .Range("I37:I41").Value
        .Range("A11").Value = .Range("A11").Value
        
        'Delete data validation dropdowns
        
        .Range("H9").Validation.Delete
        .Range("A11").Validation.Delete
        
        'Delete unwanted columns, including hidden lookup table
        
        .Columns("J:AA").Delete
    End With
            
    'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
    
    Application.DisplayAlerts = False
    
    'Delete empty sheet
    
    newWorkbook.Sheets(2).Delete
    
    'Save new workbook in xlsx format
    
    newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
    newWorkbook.Close False
    
    'Reenable Excel warnings
    
    Application.DisplayAlerts = True


End Sub

Display More

SaraO · Mar 31st 2015

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

Works beautifully!
That's amazing. Thank you so much.

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

Participate now!

Share