Edit VBA to stop deleting rows and columns

B

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.

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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
I think you just need to delete the first part (or comment it out)

Code: 
    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
 
Upvote 0
Thanks for the help!

I commented out the first part, including leaving the remaining ActiveWindow.ScrollColumn =1 at first then commenting that out to. I wasn't sure whether you wanted that line left in.

Both attempts resulted in error at:
Code: 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R65536C15", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10

What do you think is causing break?

Thanks.
 
Upvote 0
That line of code would create a pivot table from the data held in Columns A to O... Do all of the columns have headers? Is that range large enough to accommodate all the data from your report in a pivot table?
 
Upvote 0
The problem is that if you don't delete the rows & columns, you will need to rewrite, most of your code.
For instance, this
Code: 
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
is looking at Col C, but now needs to be col E the same goes for the rest of your code.
Another example
Code: 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1![COLOR=#ff0000]R1C1:R65536C15[/COLOR]", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10
This is looking at cols 1 to 15, but col 15 was originally col 27
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA Calculated Field - Subtract Value in Data Field from Value in Row Field
Replies
0
Views
207
th259
T
C
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
77
Cubist
C
T
  • Question
VBA Dynamic PivotTable Name
Replies
4
Views
480
th259
T
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
401
ummjay
U
B
  • Solved
Recording macro pivot table error
Replies
2
Views
976
biglb79
B

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top