barnesjohn
New Member
- Joined
- Dec 17, 2017
- Messages
- 2
Trying to modify VBA in macro that we use to produce payroll data. The report process generates an Excel file, but it has 2 issues that I need to solve.
1. Stop deleting first 4 rows.
2. Stop deleting any columns.
The original report (from Pentaho 3.8) creates extra columns and merged columns in the first 4 rows. This appears to be a flaw that the original report writer programmed around by having a macro delete the extra columns and first 4 rows.
The macro I'm trying to fix is applied to data pasted into xlsm file from the payroll report. We need to upgrade to Pentaho 6.1 so the original report, with its extra columns and rows, can't be used.
I can output a file in the new report without extra columns and merged columns and first 4 rows but don't know how to edit the VBA to stop deleting the extra columns (such as C, D, H). Can anyone post how to edit the VBA? I think if the VBA stops deleting any columns that the remaining code will function, creating the pivot table for the user. I tried remarking out first part of code down to ActiveWindow lines but then running macro resulted in error.
Thanks!
1. Stop deleting first 4 rows.
2. Stop deleting any columns.
The original report (from Pentaho 3.8) creates extra columns and merged columns in the first 4 rows. This appears to be a flaw that the original report writer programmed around by having a macro delete the extra columns and first 4 rows.
The macro I'm trying to fix is applied to data pasted into xlsm file from the payroll report. We need to upgrade to Pentaho 6.1 so the original report, with its extra columns and rows, can't be used.
I can output a file in the new report without extra columns and merged columns and first 4 rows but don't know how to edit the VBA to stop deleting the extra columns (such as C, D, H). Can anyone post how to edit the VBA? I think if the VBA stops deleting any columns that the remaining code will function, creating the pivot table for the user. I tried remarking out first part of code down to ActiveWindow lines but then running macro resulted in error.
Code:
Sub payroll()
'
' payroll Macro
'
'
Rows("1:4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-6
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Columns("M:M").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=6
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("P:P").Select
Selection.Delete Shift:=xlToLeft
Columns("Q:Q").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.Delete Shift:=xlToLeft
Range("B1").Select
ActiveCell.FormulaR1C1 = "DOS"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Begin"
Range("D1").Select
ActiveCell.FormulaR1C1 = "End"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Minutes"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Status"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Activity"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Procedure"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Staff units"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Client name"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Program"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Supervisor"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Signed"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Org."
Columns("C:D").Select
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R65536C15", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("STAFF_NAME")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DOS")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Begin")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("End")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Minutes")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Activity")
.Orientation = xlRowField
.Position = 8
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Staff units")
.Orientation = xlRowField
.Position = 9
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Client name")
.Orientation = xlRowField
.Position = 10
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Program")
.Orientation = xlRowField
.Position = 11
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Supervisor")
.Orientation = xlRowField
.Position = 12
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Signed")
.Orientation = xlRowField
.Position = 13
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Org.")
.Orientation = xlRowField
.Position = 14
End With
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Copy
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.Copy
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Sheet4").Select
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
With ActiveSheet.PivotTables("PivotTable1").PivotFields("STAFF_NAME2")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status2")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Supervisor2")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Signed2")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("STAFF_NAME").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DOS").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Begin").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("End").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Minutes").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Status").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Type").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Activity").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Procedure").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Staff units").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Program").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Supervisor").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Signed").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Org.").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
End Sub
Thanks!