Macro Stopped Working - Update to office 2016

C

Cobrien

New Member
Joined
Apr 30, 2015
Messages
8
I have a Macro which was working fine for months and about a week ago I got a message to say there were updates which needed to be applied to office home and business 2016.
Following the updates the macro crashes

I recorded the actions again from scratch and saved to a new macro. This also crashes.
Basically I have a csv txt file which is imported into sheet (TCB) and then 3 pivot tables created on a second sheet using the same data source but filtered for different operators
the first table is created ok and the macro stops when creating the second table. However the debug window highlights a line in code before the first table is created.

The Arrow and Yellow highlight is on the line

Cells.EntireColumn.AutoFit

About 42 lines down

any guidance would be great.


Entire code below
Code: 
Sub cashbooks()
'
' cashbooks Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    ChDir "Z:"
    Workbooks.OpenText Filename:="Z:\tcb.txt", Origin:=xlMSDOS, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
        :=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
        Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
        (3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array( _
        10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), _
        Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array( _
        36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1)), TrailingMinusNumbers _
        :=True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("G:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:S").Select
    Selection.Delete Shift:=xlToLeft
    'Columns("N:O").Select
    'Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.AutoFilter
    Range("O12").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "tcb!R1C1:R1048576C13", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
     On Error Resume Next

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Op")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cash")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cheque")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Card")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Bank")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Cash"), "Count of Cash", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Cheque"), "Count of Cheque", xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Card"), "Count of Card", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Cash")
        .Caption = "Sum of Cash"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Cheque")
        .Caption = "Sum of Cheque"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Card")
        .Caption = "Sum of Card"
        .Function = xlSum
    End With
    Range("B3:D19").Select
    Selection.Style = "Currency"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Op")
        .PivotItems("(blank)").Visible = False
    End With
    Range("F3").Select
    ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R3C6", TableName:="PivotTable2" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 6).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Op")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Cash")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Cheque")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Card")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Cash"), "Count of Cash", xlCount
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Cheque"), "Count of Cheque", xlCount
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Card"), "Count of Card", xlCount
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Cash")
        .Caption = "Sum of Cash"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Cheque")
        .Caption = "Sum of Cheque"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Card")
        .Caption = "Sum of Card"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Op")
        .PivotItems("35").Visible = False
        .PivotItems("36").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("K3").Select
    ActiveWorkbook.Worksheets("Sheet1").PivotTables("PivotTable2").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R3C11", TableName:="PivotTable3" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 11).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Op")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Cash")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Cheque")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Card")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Cash"), "Count of Cash", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Cheque"), "Count of Cheque", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Card"), "Count of Card", xlCount
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Cash")
        .Caption = "Sum of Cash"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Cheque")
        .Caption = "Sum of Cheque"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Card")
        .Caption = "Sum of Card"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Op")
        .PivotItems("2").Visible = False
        .PivotItems("4").Visible = False
        .PivotItems("3").Visible = False
        .PivotItems("6").Visible = False
        .PivotItems("7").Visible = False
        .PivotItems("8").Visible = False
        .PivotItems("12").Visible = False
        .PivotItems("22").Visible = False
        .PivotItems("13").Visible = False
        .PivotItems("25").Visible = False
        .PivotItems("30").Visible = False
        .PivotItems("31").Visible = False
        .PivotItems("32").Visible = False
        .PivotItems("33").Visible = False
        .PivotItems("34").Visible = False
        .PivotItems("37").Visible = False
        .PivotItems("38").Visible = False
        .PivotItems("39").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("A2:D2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Total For Day"
    Range("F2:I2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Newry Cash Book"
    Range("K2:N2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Banbridge Cashbook"
    Range("L21").Select
End Sub
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Cells.Select
Cells.EntireColumn.AutoFit

both still work in 2016. (sometimes they change the command, but not this case)
not sure why here. Is the cursor ON the correct sheet?
IF the command is unavailable, then the cursor (focus) is not in the correct place.
 
Last edited:
Upvote 0
ranman256 said:
Cells.Select
Cells.EntireColumn.AutoFit

both still work in 2016. (sometimes they change the command, but not this case)
not sure why here. Is the cursor ON the correct sheet?
IF the command is unavailable, then the cursor (focus) is not in the correct place.
Click to expand...

HI Ranman
I have run from developer tab and not actually opened a spreadsheet to begin with as the first line in the macro gives path and file to open.
not sure how I can see where focus is.

I have video of macro in action http://www.mediafire.com/file/fbfcd72dywf25bj/macro_crash1.avi
Any help appreciated
 
Upvote 0
ranman256 said:
THATS the problem. AutoFit only works if theres a spreadsheet open.
Click to expand...


Ranman thanks for your input the Autofit was working for 6-7 months without issue, I have commented that line out and it still fails at the same point where it is creating the 2nd pivot table.


RORYA thanks for editing the post to put code in special window, I didn't realise that could be done, how do I do that if I post code again.

thanks
 
Upvote 0
Either select the code and then click the # button above the text entry box, or manually add the tags so it looks like:

[code]
put your code here
[/code]
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro for
Replies
2
Views
96
Sanderb
S
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
193
darmst24
D
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
175
Anthony47
A
T
  • Question Question
VBA Dynamic PivotTable Name
Replies
4
Views
913
th259
T
Cubist
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
275
Cubist
Cubist

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

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