Hello, My name is Tim.
I am working on a project and in need of some help.
I have created a form in excel and inserted a button to "submit" the form.
Submit in this case needs to accomplish the following:
Copy a range from the form and save it to a new workbook, Paste the formats and the values (form will have Vlookup formulas). So far the code below has worked for this part.
Save the new workbook using a specific path and filename. Filename to be derived from a cell on the form. I get a "Method 'SaveAs of Object'_Workbook' failed" error.
When I specify the path and a filename directly in the .saveas line it seems to work.
This is as far as I have gotten so far by piecing together code I have found online. Once this is working I would like to also send the copied range (new workbook) to a network printer.
I have not found or tried any code for that part yet but would welcome any help or suggestions.
I have attached the "Form" and the code I am working with is below.
Thank you in advance for any help and suggestions, both are greatly appreciated.
Sub Submit() Dim wbI As Workbook, wbO As Workbook Dim wsI As Worksheet, wsO As Worksheet Dim path As String Dim filename1 As String '~~> Source/Input Workbook Set wbI = ThisWorkbook '~~> Set the relevant sheet from where you want to copy Set wsI = wbI.Sheets("Sheet1") '~~> Destination/Output Workbook Set wbO = Workbooks.Add With wbO '~~> Set the relevant sheet to where you want to paste Set wsO = wbO.Sheets("Sheet1") path = "y:\misc docs\" filename1 = Range("e7").Text '~~>. Save the file .SaveAs Filename:=path & filename1 & ".xls", FileFormat:=xlOpenXMLWorkbook 'Code above fails, code below works but does not fit the need. '.SaveAs Filename:="y:\misc docs\reqtest3.xlsx", FileFormat:=xlOpenXMLWorkbook '~~> Copy the range wsI.Range("B1:I42").Copy '~~> Paste it in say Cell A1. Change as applicable wsO.Range("B1").PasteSpecial Paste:=xlPasteValues wsO.Range("b1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False wbO.Close Savechanges:=True Application.CutCopyMode = False End With End Sub