I have a workbook with multiple sheets. Excel 2016.
I basically feed data from sheets 1 and 2 into a worksheet called "Prelim Estimate" (a1:f45).
I have another worksheet (worksheet5) called "Summary Data Base" which pulls the information from the "Prelim Estimate" - see below.
I would like to be able to do the following using VBA code. I have a few macros but would like to combine into one large VBA code if possible.
1. Save workbook: to a specified path in the format of
C:\Documents\Prelim Estimates\(cell e7 = customer name = C. Scott Marple ) & (cell e5 = estimate # = 6442) & (cell e6 = estimate date = 05/17/17).
2. Save worksheet "Prelim Estimate": as a PDF in format of C\documents\Prelim Estimates\same as above. This worksheet is to attach to an email with multiple recipients.
3. And/or: save the "Prelim Estimate" as a PDF to attach to an email and/or Print the PDF.
4. Copy and paste as value: the last line on worksheet "Summary Data Base" and copy formulas to next available line to prepare for the next estimate.
5. Save workbook again. Clear (see below) in Sheet 1 and Sheet 2. Renumber cell E5 on "Prelim Estimate" to the next number on "Summary Data Base" sheet Column B next number. For example, my last estimate used 6441 (B5). The next number will be 6442 (C5).
6. I have a couple of Macros already (see below) but would like to do all this as one VBA code.
Sheet one 'Assemblies' fields to clear: Name is "Clear_Assemblies" refers to -=Assemblies!$B$3,Assemblies!$B$4,Assemblies!$B$5,Assemblies!$B$6,Assemblies!$A$15,Assemblies!$B$15,Assemblies!$D$15,Assemblies!$H$13:$M$29,Assemblies!$B$20,Assemblies!$A$38,Assemblies!$B$38,Assemblies!$D$38,Assemblies!$B$43,Assemblies!$H$36:$M$52,Assemblies!$A$62,Assemblies!$B$62,Assemblies!$D$62,Assemblies!$B$67,Assemblies!$H$60:$M$76
Sheet two 'Estimate Workup' fields to clear: Name is "Clear EstWU" refers to -
='Estimate Workup'!$B$10:$G$27
<colgroup><col width="161" style="width: 121pt;">
<col width="175" style="width: 131pt;">
<col width="109" style="width: 82pt;">
<col width="98" style="width: 74pt;">
<col width="84" style="width: 63pt;">
<col width="58" style="width: 44pt;">
<tbody>
</tbody>
Sub PDF()
'
' PDF Macro
'
'
Sheets("ESTIMATE").Select
Range("A1:F45").Select
ChDir "C:\Macro Files"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Macro Files\Estimate -.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
Sub Print_Estimate()
'
' Print_Estimate Macro
'
'
ActiveWorkbook.Save
Sheets("ESTIMATE").Select
Range("A4:F45").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
I basically feed data from sheets 1 and 2 into a worksheet called "Prelim Estimate" (a1:f45).
I have another worksheet (worksheet5) called "Summary Data Base" which pulls the information from the "Prelim Estimate" - see below.
I would like to be able to do the following using VBA code. I have a few macros but would like to combine into one large VBA code if possible.
1. Save workbook: to a specified path in the format of
C:\Documents\Prelim Estimates\(cell e7 = customer name = C. Scott Marple ) & (cell e5 = estimate # = 6442) & (cell e6 = estimate date = 05/17/17).
2. Save worksheet "Prelim Estimate": as a PDF in format of C\documents\Prelim Estimates\same as above. This worksheet is to attach to an email with multiple recipients.
3. And/or: save the "Prelim Estimate" as a PDF to attach to an email and/or Print the PDF.
4. Copy and paste as value: the last line on worksheet "Summary Data Base" and copy formulas to next available line to prepare for the next estimate.
5. Save workbook again. Clear (see below) in Sheet 1 and Sheet 2. Renumber cell E5 on "Prelim Estimate" to the next number on "Summary Data Base" sheet Column B next number. For example, my last estimate used 6441 (B5). The next number will be 6442 (C5).
6. I have a couple of Macros already (see below) but would like to do all this as one VBA code.
Sheet one 'Assemblies' fields to clear: Name is "Clear_Assemblies" refers to -=Assemblies!$B$3,Assemblies!$B$4,Assemblies!$B$5,Assemblies!$B$6,Assemblies!$A$15,Assemblies!$B$15,Assemblies!$D$15,Assemblies!$H$13:$M$29,Assemblies!$B$20,Assemblies!$A$38,Assemblies!$B$38,Assemblies!$D$38,Assemblies!$B$43,Assemblies!$H$36:$M$52,Assemblies!$A$62,Assemblies!$B$62,Assemblies!$D$62,Assemblies!$B$67,Assemblies!$H$60:$M$76
Sheet two 'Estimate Workup' fields to clear: Name is "Clear EstWU" refers to -
='Estimate Workup'!$B$10:$G$27
| A | B | C | D | E | |
| 1 | Date | Estimate Number | Customer Name | Total Amount | Rep Inits |
| 2 | 5/14/2017 | 6441 | Tom Jones | 56.35 | RR |
| 3 | 5/17/2017 | 6442 | C. Scott Marple | 273.55 | ABC |
| Summary Data Base | |||||
| Worksheet Formulas | |||||
| Cell | Formula | ||||
| A3 | =+'Prelim ESTIMATE'!$E$6 | ||||
| B3 | =b2+1 | ||||
| C3 | =+'Prelim ESTIMATE'!$B$10 | ||||
| D3 | =+'Prelim ESTIMATE'!$F$41 | ||||
| E3 | =+Rep | ||||
| Workbook Defined Names | |||||
| Name | Refers To | ||||
| Rep | =Assemblies!$B$6 |
Sub PDF()
'
' PDF Macro
'
'
Sheets("ESTIMATE").Select
Range("A1:F45").Select
ChDir "C:\Macro Files"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Macro Files\Estimate -.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub
Sub Print_Estimate()
'
' Print_Estimate Macro
'
'
ActiveWorkbook.Save
Sheets("ESTIMATE").Select
Range("A4:F45").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
