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
Display More