Elliottj2121
Board Regular
- Joined
- Apr 15, 2021
- Messages
- 50
- Office Version
- 365
- 2019
- Platform
- Windows
Hello
Any help would be greatly appreciated. I am new to VBA code and I am looking for some help. I have created a workbook template that users will use to enter in trade reference data. In the workbook, I have a userform, frmapplicantname, that starts when the workbook is open prompting the user to enter in an applicant name . When the user enters the name and clicks submit I am trying to achieve these automated steps:
1. The applicant name is copied into sheet 1 cell B2.
2. A copy of the workbook is made with the data in Sheet1 Cell B2 and is named what ever is in Sheet1 Cell B2.
3. The newly named copy is then saved to a specific folder on the user's desktop.
4. When the user opens the newly named workbook the userform frmapplicantname doesn't run becaue there is data in Sheet1 Cell B2.
This is the code I have written for the command button for userform frmapplicantname, but I keep getting a error in the sub SavePath(), Line "thisworkbook.SaveCopyAs ([myfoldername2....
Private Sub cmdsubmit_Click()
FillText
SavePath
Unload Me
ActiveWorkbook.Close savechanges:=False
End Sub
Sub FillText()
Dim sh1 As Worksheet
Set sh1 = ThisWorkbook.Sheets(1)
With sh1
.Cells(1, 2) = frmapplicantname.txtapplicantname
End With
End Sub
Sub SavePath()
Dim myFolderName2 As String
Dim myFileName2 As String
myFolderName2 = Environ("userprofile") & "\Desktop\Steel_Group_References\"
If (Dir(myFolderName2, vbDirectory)) = "" Then MkDir myFolderName2
myFileName2 = txtapplicantname.Value & "" & "Steel Group References"
ThisWorkbook.SaveCopyAs ([myFolderName2 & "\" & myFileName2])
End Sub
Thank you!!!!
Any help would be greatly appreciated. I am new to VBA code and I am looking for some help. I have created a workbook template that users will use to enter in trade reference data. In the workbook, I have a userform, frmapplicantname, that starts when the workbook is open prompting the user to enter in an applicant name . When the user enters the name and clicks submit I am trying to achieve these automated steps:
1. The applicant name is copied into sheet 1 cell B2.
2. A copy of the workbook is made with the data in Sheet1 Cell B2 and is named what ever is in Sheet1 Cell B2.
3. The newly named copy is then saved to a specific folder on the user's desktop.
4. When the user opens the newly named workbook the userform frmapplicantname doesn't run becaue there is data in Sheet1 Cell B2.
This is the code I have written for the command button for userform frmapplicantname, but I keep getting a error in the sub SavePath(), Line "thisworkbook.SaveCopyAs ([myfoldername2....
Private Sub cmdsubmit_Click()
FillText
SavePath
Unload Me
ActiveWorkbook.Close savechanges:=False
End Sub
Sub FillText()
Dim sh1 As Worksheet
Set sh1 = ThisWorkbook.Sheets(1)
With sh1
.Cells(1, 2) = frmapplicantname.txtapplicantname
End With
End Sub
Sub SavePath()
Dim myFolderName2 As String
Dim myFileName2 As String
myFolderName2 = Environ("userprofile") & "\Desktop\Steel_Group_References\"
If (Dir(myFolderName2, vbDirectory)) = "" Then MkDir myFolderName2
myFileName2 = txtapplicantname.Value & "" & "Steel Group References"
ThisWorkbook.SaveCopyAs ([myFolderName2 & "\" & myFileName2])
End Sub
Thank you!!!!