The following code copies two columns on 10 different worksheets within a
workbook. The code works perfectly 12 times but on the 13th run it gets the
error run time error 1004 autofill method of range class failed. This error
happens on the line
SourceRange.AutoFill Destination:=DestinationRange, Type:=xlFillDefault
on the 7th time through the loop. Is there a limit to size of a named
range? Any other ideas why it would fail?

Sub COLUMN_COPY()
'
' COLUMN_COPY Macro
' Macro recorded 10/7/2005 by Dave Sheldon
'

'
Dim SourceRange As Range
Dim DestinationRange As Range
Dim NumColumns As Integer
Dim i As Integer

For i = 1 To 10
NumColumns = Worksheets(i).Range("col").Columns.Count
Set SourceRange = Worksheets(i).Range("col")
Set DestinationRange = Worksheets(i).Range("col").Resize(,
NumColumns + 2)
SourceRange.AutoFill Destination:=DestinationRange,
Type:=xlFillDefault
Worksheets(i).Range("col").Resize(, NumColumns + 2).Name =
Worksheets(i).Name & "!" & "col"
If Worksheets(i).Name = "ESTIMATE" Or Worksheets(i).Name = "SUMMARY"
Then
Worksheets(i).Range("col").Columns(NumColumns + 1).Hidden = True
End If
Next i

End Sub

Not sure what the Range named "col" is to start with but one possible cause
is that you are exceeding the 256 column limit of Excel when redefining the
target range??

--
Cheers
Nigel



"Dave" wrote in message
...
The following code copies two columns on 10 different worksheets within a
workbook. The code works perfectly 12 times but on the 13th run it gets
the
error run time error 1004 autofill method of range class failed. This
error
happens on the line
SourceRange.AutoFill Destination:=DestinationRange, Type:=xlFillDefault
on the 7th time through the loop. Is there a limit to size of a named
range? Any other ideas why it would fail?

Sub COLUMN_COPY()
'
' COLUMN_COPY Macro
' Macro recorded 10/7/2005 by Dave Sheldon
'

'
Dim SourceRange As Range
Dim DestinationRange As Range
Dim NumColumns As Integer
Dim i As Integer

For i = 1 To 10
NumColumns = Worksheets(i).Range("col").Columns.Count
Set SourceRange = Worksheets(i).Range("col")
Set DestinationRange = Worksheets(i).Range("col").Resize(,
NumColumns + 2)
SourceRange.AutoFill Destination:=DestinationRange,
Type:=xlFillDefault
Worksheets(i).Range("col").Resize(, NumColumns + 2).Name =
Worksheets(i).Name & "!" & "col"
If Worksheets(i).Name = "ESTIMATE" Or Worksheets(i).Name =
"SUMMARY"
Then
Worksheets(i).Range("col").Columns(NumColumns + 1).Hidden =
True
End If
Next i

End Sub

Nigel

Thanks for the response but at the point when the procedure fails the amount
of columns in the named range is 26

"Nigel" wrote:

Not sure what the Range named "col" is to start with but one possible cause
is that you are exceeding the 256 column limit of Excel when redefining the
target range??

--
Cheers
Nigel



"Dave" wrote in message
...
The following code copies two columns on 10 different worksheets within a
workbook. The code works perfectly 12 times but on the 13th run it gets
the
error run time error 1004 autofill method of range class failed. This
error
happens on the line
SourceRange.AutoFill Destination:=DestinationRange, Type:=xlFillDefault
on the 7th time through the loop. Is there a limit to size of a named
range? Any other ideas why it would fail?

Sub COLUMN_COPY()
'
' COLUMN_COPY Macro
' Macro recorded 10/7/2005 by Dave Sheldon
'

'
Dim SourceRange As Range
Dim DestinationRange As Range
Dim NumColumns As Integer
Dim i As Integer

For i = 1 To 10
NumColumns = Worksheets(i).Range("col").Columns.Count
Set SourceRange = Worksheets(i).Range("col")
Set DestinationRange = Worksheets(i).Range("col").Resize(,
NumColumns + 2)
SourceRange.AutoFill Destination:=DestinationRange,
Type:=xlFillDefault
Worksheets(i).Range("col").Resize(, NumColumns + 2).Name =
Worksheets(i).Name & "!" & "col"
If Worksheets(i).Name = "ESTIMATE" Or Worksheets(i).Name =
"SUMMARY"
Then
Worksheets(i).Range("col").Columns(NumColumns + 1).Hidden =
True
End If
Next i

End Sub