transfer data to another worksheet, save new worksheet to another folder, all closed

Hello everybody,

I am having a hard time with this one and any help would be greatly appreciated as I have spent alot of hours trying to get the code down correctly. I am trying to transfer data from a userform to another worksheet in a shared folder. After the data is transferred, I would like it to automatically save to another folder destination, all while the new workbook is on a closed basis and never having the new work book pull up. If the user wants to pull up the data again to review it, they would use the new destination folder to bring up the new file that was created. Also, I would not like to save the document where the new workbook originally generated from because it would be a blank form, so the user would just have to open up the file where it was saved to. I attached my code below. Right now it is set to open the workbook, but I would like to change that and not have the user have to ever see it. Also, in my AutosaveAIRcalc macro to save the file to another destination, I have having trouble with the FileFormat:=xlNormal wording because it gets highlighted. I have a cell in the new workbook from cell Q8 that will be the file name when it gets saved to the new folder. Thanks in advance for the help.

Sub OpenUPaircalc()

Dim nwb As Workbook

Set nwb = Workbooks.Open("\\Ad\WorkLibrary\DOCS\Calculators.xlt")

With nwb.Sheets(1)
    .Range("K40").Value = UserForm1.TextBox1.Text
    .Range("O40").Value = UserForm1.TextBox2.Text
    .Range("V40").Value = UserForm1.TextBox4.Text
    .Range("K62").Value = UserForm1.TextBox5.Text
    .Range("N62").Value = UserForm1.TextBox6.Text
    .Range("K74").Value = UserForm1.TextBox7.Text
    .Range("R74").Value = UserForm1.TextBox8.Text
    .Range("AA74").Value = UserForm1.TextBox9.Text
    .Range("K88").Value = UserForm1.TextBox10.Text
    .Range("I6").Value = Sheet1.Range("M1").Value
    .Range("Z6").Value = Sheet1.Range("L1").Value
    .Range("I8").Value = Sheet1.Range("I1").Value
    .Range("Q8").Value = Sheet1.Range("B3").Value
    .Range("Z8").Value = Sheet1.Range("L4").Value
    .Range("I10").Value = Sheet10.Range("A71").Value
    .Range("Z10").Value = Sheet10.Range("F63").Value
    .Range("I12").Value = Sheet1.Range("B6").Value
    
Call AutosaveAIRcalc

End With

End Sub

Sub AutosaveAIRcalc()
Dim fileName As String
fileName = "C:\Users\rdiea\Desktop\AIRCalcs\" & Range("Q8").Value & ".xlt"
ActiveWorkbook.SaveAs fileName:=fileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Thank you for the reply. I made the changes below and unfortunately got an errror. The first one states the followng feature cannot be saved in a macro-free workbook
*VB Project

To save a file with these features, click no, then choose a macro enabled file type in the File Type list
To continue saving as a macro free workbook, click yes

If i click no, it says VB projects and XLM Sheets cannot be saved in a macro free workbook
If I click yes, it errors out and highlights the password in my macro listed at the botton, Sub AutosaveAIRcalc()

I would like to save as macro enabled workbook, which is why I changed the format to .xlsm and not have to have any popup boxes come up, since everything will be behind the seensAny ideas? thanks again.

Sub OpenUPaircalc()

Dim nwb As Workbook

Application.ScreenUpdating = False

Set nwb = Workbooks.Open("\\Ad\WorkLibrary\DOCS\Calculators.xlt")

With nwb.Sheets(1)
    .Range("K40").Value = UserForm1.TextBox1.Text
    .Range("O40").Value = UserForm1.TextBox2.Text
    .Range("V40").Value = UserForm1.TextBox4.Text
    .Range("K62").Value = UserForm1.TextBox5.Text
    .Range("N62").Value = UserForm1.TextBox6.Text
    .Range("K74").Value = UserForm1.TextBox7.Text
    .Range("R74").Value = UserForm1.TextBox8.Text
    .Range("AA74").Value = UserForm1.TextBox9.Text
    .Range("K88").Value = UserForm1.TextBox10.Text
    .Range("I6").Value = Sheet1.Range("M1").Value
    .Range("Z6").Value = Sheet1.Range("L1").Value
    .Range("I8").Value = Sheet1.Range("I1").Value
    .Range("Q8").Value = Sheet1.Range("B3").Value
    .Range("Z8").Value = Sheet1.Range("L4").Value
    .Range("I10").Value = Sheet10.Range("A71").Value
    .Range("Z10").Value = Sheet10.Range("F63").Value
    .Range("I12").Value = Sheet1.Range("B6").Value
    
Call AutosaveAIRcalc
    
ActiveWorkbook.Close
    
End With

End Sub

Sub AutosaveAIRcalc()

Dim fileName As String
fileName = "C:\Users\rdiea\Desktop\AIRCalcs\" & Range("Q8").Value & ".xlsm"
ActiveWorkbook.SaveAs fileName:=fileName, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

OK, I think I got it figured out. All I did was change the .xlt file extension to .xlsm. that seemed to solve the problem. Everything now works great! Thank alot for the help.

This is the code I used and it transfers the data, auto saves with the cell reference, then closes right way.

Sub OpenUPaircalc()

Dim nwb As Workbook

Application.ScreenUpdating = False

Set nwb = Workbooks.Open("C:\Users\rdiea\Desktop\AIRCalcs\UW_Calculators.xlsm")

With nwb.Sheets(1)
    .Range("K40").Value = UserForm1.TextBox1.Text
    .Range("O40").Value = UserForm1.TextBox2.Text
    .Range("V40").Value = UserForm1.TextBox4.Text
    .Range("K62").Value = UserForm1.TextBox5.Text
    .Range("N62").Value = UserForm1.TextBox6.Text
    .Range("K74").Value = UserForm1.TextBox7.Text
    .Range("R74").Value = UserForm1.TextBox8.Text
    .Range("AA74").Value = UserForm1.TextBox9.Text
    .Range("K88").Value = UserForm1.TextBox10.Text
    .Range("I6").Value = Sheet1.Range("M1").Value
    .Range("Z6").Value = Sheet1.Range("L1").Value
    .Range("I8").Value = Sheet1.Range("I1").Value
    .Range("Q8").Value = Sheet1.Range("B3").Value
    .Range("Z8").Value = Sheet1.Range("L4").Value
    .Range("I10").Value = Sheet10.Range("A71").Value
    .Range("Z10").Value = Sheet10.Range("F63").Value
    .Range("I12").Value = Sheet1.Range("B6").Value
        


End With
ActiveWorkbook.SaveAs fileName:=("C:\Users\rdiea\Desktop\AIRCalcs\") & Range("Q8").Value & ".xlsm"

ActiveWorkbook.Close

Unload UserForm1

End Sub