How to create a new worksheet after data input into dialog box

[Leeward904]

I apologize if this is a duplicate post, i have not been able to find specifically what i am looking for yet.

At work we have to submit a form to ask for a crane lift and these forms need to be submitted at least 24 hours prior to the lift. What i am curious to know is that if there is a way to create a macro that is in a workbook that allows the project manager to input the job's specific details (job number, location, etc) on a summary tab and utilize a button to generate a new form for a crane lift.

Upon clicking the button the dialog box would pop up asking the user to select the specification number from a drop down list (based on the numbers from the summary tab) and a few other pieces of information. Once the data is selected in the box the user clicks "Generate Request" and the macro would take the data apply it to the "template" form on the second tab of the workbook and create a new request adding a new worksheet at the end of the workbook.

***Bonus points if the box would automatically apply request number, specification number, date submitted, and required lift date to a table on the "summary" tab.***

I am not sure if i am not searching for the right post because of the keywords i am looking for or what but any help would be greatly appreciated...

 

[vw412]

What you are describing is certainly doable. How much experience do you have with Excel and VBA? Is there an existing Excel workbook you are currently using or is this a completely new project? I am not opposed to helping you develop this but I need to know more info to help. Note also that this forum is here to assist you in using Excel not to develop apps for you.

That being said, more information will help.

 

[Leeward904]

Thank you for your reply! I just started learned VBA but i have used excel for awhile now. I have pieced together what i need so far by google, youtube, and this forum but i have hit a road block with this.

I have pieced together some of the other VBA i was able to duct tape together to get the save as PDF to a specific location, open email and attach the pdf, address the email to (based on cell from summary) and CC (based on cell from summary), add email signature. I also have the pieces to be able to copy the template worksheet and make a copy of it at the end so the user can fill out the different spots for data.

Where i am stuck is to have the sheet have the userform window pop up when the "create" button is clicked so the user fills out the information into the userform instead of typing on the created worksheet. Also i am stuck with how to get the "log" to update with certain info from the newly created request and apply the latest number in the log to the request form itself.

I have a screenshot attached to show what the "template" looks like with the cells highlighted in yellow to show where the userform applies the data from the request pop up and grey to show where the next number in line would go for the log. Also there is a screenshot of the "summary" tab to show where the log would show all of the generated requests. For some reason my work computer wont allow me to utilize the XL2BB so i can just add the who workbook for you to look at...

I apologize for not specifying first, i just need to help to add the userform pop up and add next number in log as a part of the other code. I seem to be missing something with marrying the two up.

Here is the code for the select a sheet, save as pdf in specific location, open email, attach pdf to email.

Sub MacroPDFCSR()

Dim ws As Worksheet
Dim wksAllSheets As Variant
Dim wksSheet1 As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Data")

strFName = Range("K14").Text & Range("K15").Text & Range("K16").Text & "\" & Range("K2").Text & " Crane Request " & Format(Date, "mm-dd-yyyy") & ".pdf"
strSubj = Range("K2").Text & " Crane Request " & Format(Date, "mm-dd-yyyy")

Set ws = ThisWorkbook.Sheets("THIS WOULD NEED TO BE WHATEVER THE LATEST WORKSHEET IS TITLED")

' clear any filtering of data
ws.AutoFilter.ShowAllData

' find the last row with data, to be included in print range
With Sheets("THIS WOULD NEED TO BE WHATEVER THE LATEST WORKSHEET IS TITLED")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

ws.PageSetup.PrintArea = ws.Range("A1:J" & LastRow).Address

ws.PageSetup.CenterHeader = "&10" & Worksheets("Crane Request").Range("B1").Value & Chr(10) & "Crane Request"
Range("A1").Select

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

errCheck:
If Err.Number = 1004 Then
MsgBox "Please close the Crane Request file and try again"
Exit Sub
ElseIf Err.Number = 91 Then
MsgBox "Please close the Crane Request PDF and try again"
End If

'email
Dim OApp As Object, OMail As Object, signature As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.body
With OMail
.To = ThisWorkbook.Sheets("Data").Range("K10").Text
.Cc = ThisWorkbook.Sheets("Data").Range("K11").Text
.Subject = strSubj
.Attachments.Add strFName

.body = "Please see the attached Crane Request for " & Sheets("Summary").Range("B1") & "." & Chr(10) & signature
End With

'Make sure all the worksheets are NOT left selected
Sheets("Summary").Select
Range("A1").Select

Application.ScreenUpdating = True