Autofill method of range class failed
 

Sub Donsmacro()
For Each ws In Worksheets
If Left(ws.Name, 8) < "National" And ws.Name < "TIReport (2G-3G)" Then
With ws
lastcol = .Cells(5, Columns.Count).End(xlToLeft).Column
.Rows("6").Insert
For i = 1 To lastcol
.Cells(6, i) = Application.Subtotal _
(3, .Range(.Cells(7, i), .Cells(1999, i)))
Next i
End With
End If
Next ws

End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
oops. I was doing only the 1st column. This should do it.

For Each ws In Worksheets
If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And
ws.Name
< "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI
Report
(2G-3G)" Then

With ws
lastcol = .Cells(5, Columns.Count).End(xlToLeft).Column
.Rows("6:6").Insert Shift:=xlDown
For i = 1 To lastcol
.Cells(6, i) = Application.Subtotal _
(3, .Range(.Cells(7, i), .Cells(1999, i)))
Next i
End With
end if
next ws
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Try this. I changed to find the last column in row 5 instead of 6 and
added a ) for the subtotal line.
If this doesn't work, feel free to send your workbook to my address below

For Each ws In Worksheets
If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And
ws.Name
< "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI
Report
(2G-3G)" Then

with ws
lastcol=.cells(5,columns.count).end(xltoleft).colu mn
.Rows("6:6").Insert Shift:=xlDown
.Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999"))
.Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol))
end with
End If
Next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Appache" wrote in message
...
Basicly what I want to do is to insert a row after 5th row and put a
subtotal
formula from the begining to end of 6th row.

I need to know the last column of the row in order to avoid to put this
formula for the unnecessary cells. I can not use a specific address
because
every sheet has different last column for 5th row.

--
Thanks


"Don Guillett" wrote:

For Each ws In Worksheets
If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And
ws.Name
< "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI
Report
(2G-3G)" Then
with ws
lastcol=.cells(6,columns.count).end(xltoleft).colu mn
.Rows("6:6").Insert Shift:=xlDown
' .Range("A6").Value = "=SUBTOTAL(3,A7:A1999)"
' .Range("A6").value=.Range("A6").value
'or
.Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999")
.Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol))
end with
End If
Next


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Appache" wrote in message
...
Hi,

I have an error on the code below. Here is the line that cause the
error
message.

Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6,
Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault

If I use this (when I use EY6 instead of using cells(...) )

Selection.AutoFill Destination:=ws.Range("A6:EY6"),
Type:=xlFillDefault

it doesn't give an error but I need to use the cell to find the last
column
filled in the row.


For Each ws In Worksheets
If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And
ws.Name
< "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI
Report
(2G-3G)" Then

Worksheets(ws.Index).Activate
ws.Rows("6:6").Select
Selection.Insert Shift:=xlDown

Range("A6").Value = "=SUBTOTAL(3,A7:A1999)"
ws.Range("A6").Select
Selection.Copy
ws.Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

' Selection.AutoFill Destination:=ws.Range("A6:EY6"),
Type:=xlFillDefault
Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6,
Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault

'ws.Range("A6:EY6").Select

ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address &
"").Select

End If
Next
--
Thanks