I've just written a few lines of VBA code that apply to a command button to clean up a template that I use, i.e. deleting column contents, moving info around, etc. When I press F8 and go through the VBA editor line by line, the code does what I want it to do. However, when I go to click the command back in the excel spreadsheet, it does nothing at all. The code I've written is as follows and any help would be much appreciated!
Private Sub CommandButton_Click5()
'ASK IF THEY WANT TO USE THE BUTTON
response = MsgBox("Prepare inventory template ready for consolidation?", vbYesNo, "Confirm")
lineNo = 1
If (response = VbMsgBoxResult.vbYes) Then
'OPEN UP THE TEMPLATE PRE-CLEANING
Workbooks.Open fileName:="c:\KPMG\MoW KPMG Report Consolidated Report TEMPLATE.xlt"
'GO TO THE SUMMARY SHEET 'CLEAR J:K
Worksheets("Baseline Inventory - Summary").Activate
Worksheets("Baseline Inventory - Summary").Range("J2:K1000").ClearContents
'PASTE M:N IN TO J:K
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").Copy _
Destination:=Worksheets("Baseline Inventory - Summary").Range("J2")
'CLEAR M:N
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").ClearContents
'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd
'CLEAR COLUMNS F:H AND P:R
Worksheets("Baseline Inventory - Summary").Range("F2:H1000").ClearContents
Worksheets("Baseline Inventory - Summary").Range("P2:R1000").ClearContents
'REMOVE FILTER
Worksheets("Baseline Inventory - Summary").AutoFilterMode = False
'GO TO BASELINE INVENTORY FULL
Worksheets("Baseline Inventory - Full").Activate
'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd
'CLEAR COLUMNS F:I AND K:L
Worksheets("Baseline Inventory - Full").Range("F2:I1000").ClearContents
Worksheets("Baseline Inventory - Full").Range("K2:L1000").ClearContents
'REMOVE DATA FILTER
Worksheets("Baseline Inventory - Full").AutoFilterMode = False
'SAVE AS {MOW KPMG REPORT TEMPLATE.XLT} IN C:/KPMG
ActiveWorkbook.Save
End If
End Sub
Private Sub CommandButton_Click5()
'ASK IF THEY WANT TO USE THE BUTTON
response = MsgBox("Prepare inventory template ready for consolidation?", vbYesNo, "Confirm")
lineNo = 1
If (response = VbMsgBoxResult.vbYes) Then
'OPEN UP THE TEMPLATE PRE-CLEANING
Workbooks.Open fileName:="c:\KPMG\MoW KPMG Report Consolidated Report TEMPLATE.xlt"
'GO TO THE SUMMARY SHEET 'CLEAR J:K
Worksheets("Baseline Inventory - Summary").Activate
Worksheets("Baseline Inventory - Summary").Range("J2:K1000").ClearContents
'PASTE M:N IN TO J:K
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").Copy _
Destination:=Worksheets("Baseline Inventory - Summary").Range("J2")
'CLEAR M:N
Worksheets("Baseline Inventory - Summary").Range("M2:N1000").ClearContents
'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd
'CLEAR COLUMNS F:H AND P:R
Worksheets("Baseline Inventory - Summary").Range("F2:H1000").ClearContents
Worksheets("Baseline Inventory - Summary").Range("P2:R1000").ClearContents
'REMOVE FILTER
Worksheets("Baseline Inventory - Summary").AutoFilterMode = False
'GO TO BASELINE INVENTORY FULL
Worksheets("Baseline Inventory - Full").Activate
'FILTER BY NOT EQUAL TO COMPLETE IN COLUMN K
Selection.AutoFilter Field:=11, Criteria1:="<>COMPLETE", Operator:=xlAnd
'CLEAR COLUMNS F:I AND K:L
Worksheets("Baseline Inventory - Full").Range("F2:I1000").ClearContents
Worksheets("Baseline Inventory - Full").Range("K2:L1000").ClearContents
'REMOVE DATA FILTER
Worksheets("Baseline Inventory - Full").AutoFilterMode = False
'SAVE AS {MOW KPMG REPORT TEMPLATE.XLT} IN C:/KPMG
ActiveWorkbook.Save
End If
End Sub