G
Guest
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
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