ChloeDage22
New Member
- Joined
- Sep 28, 2020
- Messages
- 6
- Office Version
- 365
Below is my code. I'm having issues compiling. My goal is to have people fill out an assesment form and that information gets stored on a seprate workbook. It also should generate a dashboard made of Pivot charts. Any suggestions as to why it isnt working
'Can line Mechanics
Sub start_menu_CL()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("L1 CL").Activate
clear_input
End Sub
Sub save_nxt_L1_CL()
Call copytolog("L1 CL")
End Sub
Sub save_nxt_L2_CL()
Call copytolog("L2 CL")
End Sub
Sub save_nxt_L7_CL()
Call copytolog("L7 CL")
End Sub
'Electricians
Sub start_menu()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("BIB").Activate
clear_input
End Sub
Sub save_nxt_L1()
Call copytolog("L1")
End Sub
Sub save_nxt_L2()
Call copytolog("L2")
End Sub
Sub save_nxt_L3()
Call copytolog("L3")
End Sub
Sub save_nxt_L4()
Call copytolog("L4")
End Sub
Sub save_nxt_L5()
Call copytolog("L5")
End Sub
Sub save_nxt_L7()
Call copytolog("L7")
End Sub
'Bottle Line Mechanics
Sub start_menu_BL()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("BIB BL").Activate
clear_input
End Sub
Sub save_nxt_L3_BL()
Call copytolog("L3 BL")
End Sub
Sub save_nxt_L4_BL()
Call copytolog("L4 BL")
End Sub
Sub save_nxt_L5_BL()
Call copytolog("L5 BL")
End Sub
'Central Services
Sub start_menu_Syrup()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Syrup Room").Activate
clear_input
End Sub
Sub save_nxt_Ammonia()
Call copytolog("Ammonia")
End Sub
Sub save_nxt_Boiler()
Call copytolog("Boiler")
End Sub
Sub save_nxt_Water()
Call copytolog("Water Treatment")
End Sub
Function copytolog(next_page As String)
Application.ScreenUpdating = False
Range("B6:G" & Range("c1000").End(xlUp).Row).Copy
ThisWorkbook.Sheets("DataLog").Activate
Range("A65536").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial xlPasteValues
ThisWorkbook.Sheets(next_page).Activate
clear_input
End Function
Sub Export_PDF()
refreshh
Print_to_pdf
End Sub
Sub save_end()
Application.ScreenUpdating = False
Call copytolog("Dashboard")
ResizeList
refreshh
End Sub
Sub clear_input()
Range("D6:F" & Range("c1000").End(xlUp).Row).ClearContents
End Sub
Function set_name_pvt(pvttable As String) As Boolean
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
Set pt = Worksheets("Tables").PivotTables(pvttable)
Set Field = pt.PivotFields("Name")
NewCat = Range("mech_name")
On Error GoTo errorhandle
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
set_name_pvt = False
Exit Function
errorhandle:
MsgBox ("Nobody by that name hasdone a skill assesment yet.")
set_name_pvt = True
End Function
Sub ResizeList()
Application.ScreenUpdating = False
Dim wrksht As Worksheet
Dim objListObj As ListObject
Set wrksht = ActiveWorkbook.Worksheets("DataLog")
Set objListObj = wrksht.ListObjects(1)
ActiveWorkbook.Worksheets("DataLog").Activate
objListObj.Resize Range("A1:G" & Range("a65000").End(xlUp).Row)
ActiveWorkbook.Worksheets("Dashboard").Activate
End Sub
Sub goto_report()
refreshh
ActiveWorkbook.Worksheets("Dashboard").Activate
End Sub
Sub refreshh()
Dim pc As PivotCache
ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections
For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next pc
If set_name_pvt("pivotTable1") Then Exit Sub
set_name_pvt ("pivotTable2")
set_name_pvt ("pivotTable4")
set_name_pvt ("pivotTable5")
End Sub
Sub Print_to_pdf()
Dim WR_image_dir As String
With Sheets("Dashboard").PageSetup
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
End With
WR_image_dir = Sheets("parameters").Range("F5").Value & "\" & Sheets("parameters").Range("F3").Value & ".pdf"
Sheets("Dashboard").Range(Names("Print_PDF_Range").RefersToRange(1, 1)).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
WR_image_dir, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
'Can line Mechanics
Sub start_menu_CL()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("L1 CL").Activate
clear_input
End Sub
Sub save_nxt_L1_CL()
Call copytolog("L1 CL")
End Sub
Sub save_nxt_L2_CL()
Call copytolog("L2 CL")
End Sub
Sub save_nxt_L7_CL()
Call copytolog("L7 CL")
End Sub
'Electricians
Sub start_menu()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("BIB").Activate
clear_input
End Sub
Sub save_nxt_L1()
Call copytolog("L1")
End Sub
Sub save_nxt_L2()
Call copytolog("L2")
End Sub
Sub save_nxt_L3()
Call copytolog("L3")
End Sub
Sub save_nxt_L4()
Call copytolog("L4")
End Sub
Sub save_nxt_L5()
Call copytolog("L5")
End Sub
Sub save_nxt_L7()
Call copytolog("L7")
End Sub
'Bottle Line Mechanics
Sub start_menu_BL()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("BIB BL").Activate
clear_input
End Sub
Sub save_nxt_L3_BL()
Call copytolog("L3 BL")
End Sub
Sub save_nxt_L4_BL()
Call copytolog("L4 BL")
End Sub
Sub save_nxt_L5_BL()
Call copytolog("L5 BL")
End Sub
'Central Services
Sub start_menu_Syrup()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Syrup Room").Activate
clear_input
End Sub
Sub save_nxt_Ammonia()
Call copytolog("Ammonia")
End Sub
Sub save_nxt_Boiler()
Call copytolog("Boiler")
End Sub
Sub save_nxt_Water()
Call copytolog("Water Treatment")
End Sub
Function copytolog(next_page As String)
Application.ScreenUpdating = False
Range("B6:G" & Range("c1000").End(xlUp).Row).Copy
ThisWorkbook.Sheets("DataLog").Activate
Range("A65536").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial xlPasteValues
ThisWorkbook.Sheets(next_page).Activate
clear_input
End Function
Sub Export_PDF()
refreshh
Print_to_pdf
End Sub
Sub save_end()
Application.ScreenUpdating = False
Call copytolog("Dashboard")
ResizeList
refreshh
End Sub
Sub clear_input()
Range("D6:F" & Range("c1000").End(xlUp).Row).ClearContents
End Sub
Function set_name_pvt(pvttable As String) As Boolean
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
Set pt = Worksheets("Tables").PivotTables(pvttable)
Set Field = pt.PivotFields("Name")
NewCat = Range("mech_name")
On Error GoTo errorhandle
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
set_name_pvt = False
Exit Function
errorhandle:
MsgBox ("Nobody by that name hasdone a skill assesment yet.")
set_name_pvt = True
End Function
Sub ResizeList()
Application.ScreenUpdating = False
Dim wrksht As Worksheet
Dim objListObj As ListObject
Set wrksht = ActiveWorkbook.Worksheets("DataLog")
Set objListObj = wrksht.ListObjects(1)
ActiveWorkbook.Worksheets("DataLog").Activate
objListObj.Resize Range("A1:G" & Range("a65000").End(xlUp).Row)
ActiveWorkbook.Worksheets("Dashboard").Activate
End Sub
Sub goto_report()
refreshh
ActiveWorkbook.Worksheets("Dashboard").Activate
End Sub
Sub refreshh()
Dim pc As PivotCache
ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections
For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next pc
If set_name_pvt("pivotTable1") Then Exit Sub
set_name_pvt ("pivotTable2")
set_name_pvt ("pivotTable4")
set_name_pvt ("pivotTable5")
End Sub
Sub Print_to_pdf()
Dim WR_image_dir As String
With Sheets("Dashboard").PageSetup
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
End With
WR_image_dir = Sheets("parameters").Range("F5").Value & "\" & Sheets("parameters").Range("F3").Value & ".pdf"
Sheets("Dashboard").Range(Names("Print_PDF_Range").RefersToRange(1, 1)).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
WR_image_dir, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub