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
- '~~> 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