Excel 2013 "400" Macro Error Help Needed

[skidragon02]

Hello,

I am using the following Macro in Excel 2013 and receiving an error message that simply says "400" and prevents the macro from adjusting my margins appropriately. Does someone know what is causing the problem? Thanks in advance for the help!

Sub Column_Widths()

Dim i As Integer

Dim ws As Worksheet
Application.ScreenUpdating = False
For i = 3 To 11
  With Sheets(i)
  .Columns("A:A").ColumnWidth = 5.57
  .Columns("B:B").ColumnWidth = 11.71
  .Columns("C:C").ColumnWidth = 8.86
  .Columns("D:D").ColumnWidth = 8.86
  .Columns("E:E").ColumnWidth = 9.43
  .Columns("F:F").ColumnWidth = 2.86
  .Columns("G:G").ColumnWidth = 17#
  .Columns("H:H").ColumnWidth = 7.29
  .Columns("I:I").ColumnWidth = 32#
  .Columns("J:J").ColumnWidth = 32#
  .Columns("K:K").ColumnWidth = 16.29
  .Columns("L:L").ColumnWidth = 7.71
  .Columns("M:M").ColumnWidth = 10.29
  .Columns("N:N").ColumnWidth = 4.86
  .Columns("O:O").ColumnWidth = 7.14
  .Columns("P:P").ColumnWidth = 4.86
  .Columns("Q:Q").ColumnWidth = 6.29
  .Columns("R:R").ColumnWidth = 5.57
  .Rows("3:183").EntireRow.AutoFit
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Luke M]

Hmm. Not sure what's the problem. As a work-around, and possible improvement, if you create a template sheet somewhere, you could change the code to be:

Sub Column_Widths()
Dim i As Integer

Application.ScreenUpdating = False
'Some random sheet that has column widths already set
Worksheets("My template").Cells.Copy
For i = 3 To 11
    With Sheets(i)
      .Cells.PasteSpecial (xlPasteColumnWidths)
      .Rows("3:183").EntireRow.AutoFit
    End With
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Luke M]

One quick thing, you don't happen to have any chart sheets in your workbook? I just noticed that your code says "Sheets(i)", so it's possible a Chart sheet could be selected, which would error out if you try to adjust rows/columns.

 

[skidragon02]

One quick thing, you don't happen to have any chart sheets in your workbook? I just noticed that your code says "Sheets(i)", so it's possible a Chart sheet could be selected, which would error out if you try to adjust rows/columns.

I don't believe so. I am using formulas and conditional formatting within tables, but nothing beyond that.

 

[NARAYANK991]

Hi ,

When the code errors out , has it completed at least one worksheet ? Or does it error out on the very first sheet ?

I tried out the code , and it does not give any error.

You can comment out the statement :

Application.ScreenUpdating = False

at the beginning , and see if you get any more informative error message.

Narayan

 

[rumshar]

There must be some hidden sheets like chart sheet....I tried with your code and it works perfectly.But when I added a chartsheet it threw error. In your code, I replaced 'sheet' with 'worksheet' and it worked.

With Worksheets(i) ' use this code

With Regards
Rudra

 

[skidragon02]

@narayank991 - No, it does not complete any worksheets.

@rumshar - I have no hidden sheets.



I will try both of your suggestiosn and hope for the best.

 

[AIM]

Hi,

In case you are still getting an error, can you add an Error check to find out what the Error description is.

Add this line after "Application.ScreenUpdating = False"

On Error GoTo ErrorChk

and at the end of the code after "Next" add the below lines

exit sub

ErrorChk:
MsgBox Err.Description

Now run your code and you should get a description of the error that you are facing.

Regards!!