Copying VBA Code between tabs

[nrodrigues23]

I have written a code to automatically update the width of columns based on what is in a particular range of cells. See the code below.

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Range("A10:DC49").Columns.AutoFit
For i = 1 To ActiveSheet.Range("A:DC").Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth - 0.35
Next i
Application.ScreenUpdating = True
End Sub

Now that I have gotten this code to work I would like to set the same thing up in other tabs. I keep getting a Run-time error every time I try to copy this to another tab in my workbook. Do I have to assign the code to something other than "activesheet" and if so, what does the code look like to do that?

Thanks.

 

[Fluff]

What error message do you get?
You should be able to copy/paste that code into any sheet module without problems.

 

[nrodrigues23]

The error says:
Run-time error '1004':
Unable to set the ColumnWidth property of the Range Class

I have looked this up but it sounds like it could be a variety of issues.

 

[Fluff]

Do you have any merged cells on those other sheets?
Also are any of them protected?

 

[mikerickson]

Is it possible that

Columns(i).ColumnWidth = Columns(i).ColumnWidth - 0.35

is trying to set the ColumnWidth to a negative number?

 

[nrodrigues23]

I have merged cells in the headers, none of which fall into the range i am specifying in the code. Also, none of these sheets are protected. The sheets that I am trying to copy the code over to are essentially copys of the 1st sheet the code is working on.

 

[nrodrigues23]

So I tried to run this with a + 0.35 and it did run. However, I am trying to make the columns 0.35 smaller than what autofit will give you. Do you have a suggestion for cleaning this code up? I am not sure why the - 0.35 worked on one tab and not another

 

[mikerickson]

Set a minimum width

Columns(i).ColumnWidth = WorksheetFunction.Max(.5, Columns(i).ColumnWidth - 0.35)