Emoncada
01-11-2008, 09:23 AM
I have this code that I found and modified for my needs
If UserForm1.CmbBoxProject.Value = "MORTGAGE" Then
Dim n As Worksheet
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count
Sheets("Mortgage PS").Select
Sheets("Mortgage PS").Copy After:=Worksheets(TotalSheets)
Dim MyValue
MyValue = UserForm1.TxtOrdNum.Value
ActiveSheet.Name = MyValue
Range("B7").Value = UserForm1.TxtShippedBy.Value
Range("B8").Value = UserForm1.TxtOrdNum.Value
Range("B9").Value = UserForm1.TxtShipDate.Value
Range("D7").Value = UserForm1.CmbBoxClientName.Value
Range("D8").Value = UserForm1.CmbBoxLocation.Value
Range("D9").Value = UserForm1.LblShipVia.Caption
'Enter Sheet 1 Tracking
Range("A13").Value = UserForm1.TxtTrack1.Value
Range("A14").Value = UserForm1.TxtTrack2.Value
Range("A15").Value = UserForm1.TxtTrack3.Value
Range("A16").Value = UserForm1.TxtTrack4.Value
Range("A17").Value = UserForm1.TxtTrack5.Value
Range("A18").Value = UserForm1.TxtTrack6.Value
Range("A19").Value = UserForm1.TxtTrack7.Value
Range("A20").Value = UserForm1.TxtTrack8.Value
Range("A21").Value = UserForm1.TxtTrack9.Value
Range("A22").Value = UserForm1.TxtTrack10.Value
Range("A23").Value = UserForm1.TxtTrack11.Value
Range("A24").Value = UserForm1.TxtTrack12.Value
Range("A25").Value = UserForm1.TxtTrack13.Value
Range("A26").Value = UserForm1.TxtTrack14.Value
Range("A27").Value = UserForm1.TxtTrack15.Value
Range("A28").Value = UserForm1.TxtTrack16.Value
Range("A29").Value = UserForm1.TxtTrack17.Value
Range("A30").Value = UserForm1.TxtTrack18.Value
Range("A31").Value = UserForm1.TxtTrack19.Value
'Enter Sheet 1 Description
Range("B13").Value = UserForm1.TxtSN1.Value
Range("B14").Value = UserForm1.TxtSN2.Value
Range("B15").Value = UserForm1.TxtSN3.Value
Range("B16").Value = UserForm1.TxtSN4.Value
Range("B17").Value = UserForm1.TxtSN5.Value
Range("B18").Value = UserForm1.TxtSN6.Value
Range("B19").Value = UserForm1.TxtSN7.Value
Range("B20").Value = UserForm1.TxtSN8.Value
Range("B21").Value = UserForm1.TxtSN9.Value
Range("B22").Value = UserForm1.TxtSN10.Value
Range("B23").Value = UserForm1.TxtSN11.Value
Range("B24").Value = UserForm1.TxtSN12.Value
Range("B25").Value = UserForm1.TxtSN13.Value
Range("B26").Value = UserForm1.TxtSN14.Value
Range("B27").Value = UserForm1.TxtSN15.Value
Range("B28").Value = UserForm1.TxtSN16.Value
Range("B29").Value = UserForm1.TxtSN17.Value
Range("B30").Value = UserForm1.TxtSN18.Value
Range("B31").Value = UserForm1.TxtSn19.Value
'Enter Sheet 1 Serial Number
Range("C13").Value = UserForm1.CmbBoxDesc1.Value
Range("C14").Value = UserForm1.CmbBoxDesc2.Value
Range("C15").Value = UserForm1.CmbBoxDesc3.Value
Range("C16").Value = UserForm1.CmbBoxDesc4.Value
Range("C17").Value = UserForm1.CmbBoxDesc5.Value
Range("C18").Value = UserForm1.CmbBoxDesc6.Value
Range("C19").Value = UserForm1.CmbBoxDesc7.Value
Range("C20").Value = UserForm1.CmbBoxDesc8.Value
Range("C21").Value = UserForm1.CmbBoxDesc9.Value
Range("C22").Value = UserForm1.CmbBoxDesc10.Value
Range("C23").Value = UserForm1.CmbBoxDesc11.Value
Range("C24").Value = UserForm1.CmbBoxDesc12.Value
Range("C25").Value = UserForm1.CmbBoxDesc13.Value
Range("C26").Value = UserForm1.CmbBoxDesc14.Value
Range("C27").Value = UserForm1.CmbBoxDesc15.Value
Range("C28").Value = UserForm1.CmbBoxDesc16.Value
Range("C29").Value = UserForm1.CmbBoxDesc17.Value
Range("C30").Value = UserForm1.CmbBoxDesc18.Value
Range("C31").Value = UserForm1.CmbBoxDesc19.Value
'Enter Sheet 1 Quantity
Range("D13").Value = UserForm1.TxtQua1.Value
Range("D14").Value = UserForm1.TxtQua2.Value
Range("D15").Value = UserForm1.TxtQua3.Value
Range("D16").Value = UserForm1.TxtQua4.Value
Range("D17").Value = UserForm1.TxtQua5.Value
Range("D18").Value = UserForm1.TxtQua6.Value
Range("D19").Value = UserForm1.TxtQua7.Value
Range("D20").Value = UserForm1.TxtQua8.Value
Range("D21").Value = UserForm1.TxtQua9.Value
Range("D22").Value = UserForm1.TxtQua10.Value
Range("D23").Value = UserForm1.TxtQua11.Value
Range("D24").Value = UserForm1.TxtQua12.Value
Range("D25").Value = UserForm1.TxtQua13.Value
Range("D26").Value = UserForm1.TxtQua14.Value
Range("D27").Value = UserForm1.TxtQua15.Value
Range("D28").Value = UserForm1.TxtQua16.Value
Range("D29").Value = UserForm1.TxtQua17.Value
Range("D30").Value = UserForm1.TxtQua18.Value
Range("D31").Value = UserForm1.TxtQua19.Value
Range("A32").Value = UserForm1.TxtComments.Value
'' ****remove formulae & external links****
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Sheets("Packing Slip Pim").Select
End If
How can Instead of having it open a new worksheet have it open a new Workbook and then save it there and close it?
If UserForm1.CmbBoxProject.Value = "MORTGAGE" Then
Dim n As Worksheet
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count
Sheets("Mortgage PS").Select
Sheets("Mortgage PS").Copy After:=Worksheets(TotalSheets)
Dim MyValue
MyValue = UserForm1.TxtOrdNum.Value
ActiveSheet.Name = MyValue
Range("B7").Value = UserForm1.TxtShippedBy.Value
Range("B8").Value = UserForm1.TxtOrdNum.Value
Range("B9").Value = UserForm1.TxtShipDate.Value
Range("D7").Value = UserForm1.CmbBoxClientName.Value
Range("D8").Value = UserForm1.CmbBoxLocation.Value
Range("D9").Value = UserForm1.LblShipVia.Caption
'Enter Sheet 1 Tracking
Range("A13").Value = UserForm1.TxtTrack1.Value
Range("A14").Value = UserForm1.TxtTrack2.Value
Range("A15").Value = UserForm1.TxtTrack3.Value
Range("A16").Value = UserForm1.TxtTrack4.Value
Range("A17").Value = UserForm1.TxtTrack5.Value
Range("A18").Value = UserForm1.TxtTrack6.Value
Range("A19").Value = UserForm1.TxtTrack7.Value
Range("A20").Value = UserForm1.TxtTrack8.Value
Range("A21").Value = UserForm1.TxtTrack9.Value
Range("A22").Value = UserForm1.TxtTrack10.Value
Range("A23").Value = UserForm1.TxtTrack11.Value
Range("A24").Value = UserForm1.TxtTrack12.Value
Range("A25").Value = UserForm1.TxtTrack13.Value
Range("A26").Value = UserForm1.TxtTrack14.Value
Range("A27").Value = UserForm1.TxtTrack15.Value
Range("A28").Value = UserForm1.TxtTrack16.Value
Range("A29").Value = UserForm1.TxtTrack17.Value
Range("A30").Value = UserForm1.TxtTrack18.Value
Range("A31").Value = UserForm1.TxtTrack19.Value
'Enter Sheet 1 Description
Range("B13").Value = UserForm1.TxtSN1.Value
Range("B14").Value = UserForm1.TxtSN2.Value
Range("B15").Value = UserForm1.TxtSN3.Value
Range("B16").Value = UserForm1.TxtSN4.Value
Range("B17").Value = UserForm1.TxtSN5.Value
Range("B18").Value = UserForm1.TxtSN6.Value
Range("B19").Value = UserForm1.TxtSN7.Value
Range("B20").Value = UserForm1.TxtSN8.Value
Range("B21").Value = UserForm1.TxtSN9.Value
Range("B22").Value = UserForm1.TxtSN10.Value
Range("B23").Value = UserForm1.TxtSN11.Value
Range("B24").Value = UserForm1.TxtSN12.Value
Range("B25").Value = UserForm1.TxtSN13.Value
Range("B26").Value = UserForm1.TxtSN14.Value
Range("B27").Value = UserForm1.TxtSN15.Value
Range("B28").Value = UserForm1.TxtSN16.Value
Range("B29").Value = UserForm1.TxtSN17.Value
Range("B30").Value = UserForm1.TxtSN18.Value
Range("B31").Value = UserForm1.TxtSn19.Value
'Enter Sheet 1 Serial Number
Range("C13").Value = UserForm1.CmbBoxDesc1.Value
Range("C14").Value = UserForm1.CmbBoxDesc2.Value
Range("C15").Value = UserForm1.CmbBoxDesc3.Value
Range("C16").Value = UserForm1.CmbBoxDesc4.Value
Range("C17").Value = UserForm1.CmbBoxDesc5.Value
Range("C18").Value = UserForm1.CmbBoxDesc6.Value
Range("C19").Value = UserForm1.CmbBoxDesc7.Value
Range("C20").Value = UserForm1.CmbBoxDesc8.Value
Range("C21").Value = UserForm1.CmbBoxDesc9.Value
Range("C22").Value = UserForm1.CmbBoxDesc10.Value
Range("C23").Value = UserForm1.CmbBoxDesc11.Value
Range("C24").Value = UserForm1.CmbBoxDesc12.Value
Range("C25").Value = UserForm1.CmbBoxDesc13.Value
Range("C26").Value = UserForm1.CmbBoxDesc14.Value
Range("C27").Value = UserForm1.CmbBoxDesc15.Value
Range("C28").Value = UserForm1.CmbBoxDesc16.Value
Range("C29").Value = UserForm1.CmbBoxDesc17.Value
Range("C30").Value = UserForm1.CmbBoxDesc18.Value
Range("C31").Value = UserForm1.CmbBoxDesc19.Value
'Enter Sheet 1 Quantity
Range("D13").Value = UserForm1.TxtQua1.Value
Range("D14").Value = UserForm1.TxtQua2.Value
Range("D15").Value = UserForm1.TxtQua3.Value
Range("D16").Value = UserForm1.TxtQua4.Value
Range("D17").Value = UserForm1.TxtQua5.Value
Range("D18").Value = UserForm1.TxtQua6.Value
Range("D19").Value = UserForm1.TxtQua7.Value
Range("D20").Value = UserForm1.TxtQua8.Value
Range("D21").Value = UserForm1.TxtQua9.Value
Range("D22").Value = UserForm1.TxtQua10.Value
Range("D23").Value = UserForm1.TxtQua11.Value
Range("D24").Value = UserForm1.TxtQua12.Value
Range("D25").Value = UserForm1.TxtQua13.Value
Range("D26").Value = UserForm1.TxtQua14.Value
Range("D27").Value = UserForm1.TxtQua15.Value
Range("D28").Value = UserForm1.TxtQua16.Value
Range("D29").Value = UserForm1.TxtQua17.Value
Range("D30").Value = UserForm1.TxtQua18.Value
Range("D31").Value = UserForm1.TxtQua19.Value
Range("A32").Value = UserForm1.TxtComments.Value
'' ****remove formulae & external links****
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Sheets("Packing Slip Pim").Select
End If
How can Instead of having it open a new worksheet have it open a new Workbook and then save it there and close it?