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?
Save single sheet as PDF & .xls, transfer data to master sheet, email single sheet
-
-
-
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
Display MorePrivate 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
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;742040For 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
Display MorePublic 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
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.
Code
Display MorePublic 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
-
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
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.
-
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
Display MorePublic 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
-
Re: Save single sheet as PDF & .xls, transfer data to master sheet, email single shee
Works beautifully!
That's amazing. Thank you so much.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!