Hi,

This works:

Worksheets("Sheet1").Range("G7").AutoFill Destination:=Range("G7:K7"),
Type:=xlFillDefault

but this doesn't ...

Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,13)), Type:=xlFillDefault

Can anyone suggest a way to autofill some column headings for numeric
number of columns?

Ideally, I'd like to do something like this:

Dim MyColumns as Integer
MyColumns = 8
Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,7 + MyColumns)),
Type:=xlFillDefault

Any help would be appreciated.

Greg

It works fine if Sheet1 is the activesheet. otherwsie try

With Worksheets("Sheet1")
.Range("G7").AutoFill Destination:=.Range(.CELLS(7,7),.CELLS(7,13)),
Type:=xlFillDefault
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Greg Glynn" wrote in message
ups.com...
Hi,

This works:

Worksheets("Sheet1").Range("G7").AutoFill Destination:=Range("G7:K7"),
Type:=xlFillDefault

but this doesn't ...

Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,13)), Type:=xlFillDefault

Can anyone suggest a way to autofill some column headings for numeric
number of columns?

Ideally, I'd like to do something like this:

Dim MyColumns as Integer
MyColumns = 8
Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,7 + MyColumns)),
Type:=xlFillDefault

Any help would be appreciated.

Greg

Greg,

All of what you have posted works OK, what's the issue?

Mike

"Greg Glynn" wrote:

Hi,

This works:

Worksheets("Sheet1").Range("G7").AutoFill Destination:=Range("G7:K7"),
Type:=xlFillDefault

but this doesn't ...

Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,13)), Type:=xlFillDefault

Can anyone suggest a way to autofill some column headings for numeric
number of columns?

Ideally, I'd like to do something like this:

Dim MyColumns as Integer
MyColumns = 8
Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,7 + MyColumns)),
Type:=xlFillDefault

Any help would be appreciated.

Greg

I've got good news and bad news. The good news is all 3 of your codes works
fine for me (XL2K, WinXP). The bad news is it doesn't work for you. So there
must be some other underlying issue besides the code.

Mike F
"Greg Glynn" wrote in message
ups.com...
Hi,

This works:

Worksheets("Sheet1").Range("G7").AutoFill Destination:=Range("G7:K7"),
Type:=xlFillDefault

but this doesn't ...

Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,13)), Type:=xlFillDefault

Can anyone suggest a way to autofill some column headings for numeric
number of columns?

Ideally, I'd like to do something like this:

Dim MyColumns as Integer
MyColumns = 8
Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,7 + MyColumns)),
Type:=xlFillDefault

Any help would be appreciated.

Greg

Hi Bob,

I changed my code from "Thisworksheet.sheets.range( ...." to the "with/
end with" structure as you suggested. I'm getting "autofill method of
range class failed" on the last line (before the 'end with'). Maybe I
need to Activate the sheet?

With Worksheets("MyQuery")

.Range(Cells(5, 1), Cells(65000, 255)).Clear
.Range(Cells(4, 6), Cells(4, 255)).Clear
.Range("A6").Value = "Agency"
.Range("B6").Value = "Server"
.Range("C6").Value = "Policy"
.Range("D6").Value = "Description"
.Rows("6:6").Font.Bold = True

ReportDays = ReportCriteria.PickFinishDate -
ReportCriteria.PickStartDate

'Write the Dates for the Grid Column Headings
ReDim DateArray(ReportDays + 1)

For i = 0 To ReportDays
.Cells(6, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(6, 6 + i).NumberFormat = "dd mmm"
.Cells(6, 6 + i).HorizontalAlignment = xlCenter

.Cells(5, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(5, 6 + i).NumberFormat = "ddd"
.Cells(5, 6 + i).HorizontalAlignment = xlCenter
DateArray(i + 1) = FormatDateTime(ReportCriteria.PickStartDate + i,
vbShortDate)
Next i

.Range("F4").AutoFill Destination:=.Range(Cells(4, 5), Cells(4,
12)), Type:=xlFillDefault
End With

Here's the fix:

Dim MyRange as Range
Set MyRange=.Range(Cells(7,7),Cells(7,14))
..Range("G7").AutoFill Destination:=MyRange, Type:=xlFillDefault

Apparently the Destination:= keyword needs to receive a Range Object,
and .Range(Cells(7,7),Cells(7,14)) doesn't cut the mustard.

You also have trouble waiting to happen he

With Worksheets("MyQuery")
.Range(Cells(5, 1), Cells(65000, 255)).Clear
.Range(Cells(4, 6), Cells(4, 255)).Clear

Those cells() are unqualified. If myQuery isn't the activesheet, it'll fail.

With Worksheets("MyQuery")
.Range(.Cells(5, 1), .Cells(65000, 255)).Clear
.Range(.Cells(4, 6), .Cells(4, 255)).Clear

Same he

.Range("F4").AutoFill Destination:=.Range(.Cells(4, 5), .Cells(4, 12)), _
Type:=xlFillDefault

or
.Range("F4").AutoFill Destination:=.Range("F4", .Cells(4, 12)), _
Type:=xlFillDefault



Greg Glynn wrote:

Hi Bob,

I changed my code from "Thisworksheet.sheets.range( ...." to the "with/
end with" structure as you suggested. I'm getting "autofill method of
range class failed" on the last line (before the 'end with'). Maybe I
need to Activate the sheet?

With Worksheets("MyQuery")

.Range(Cells(5, 1), Cells(65000, 255)).Clear
.Range(Cells(4, 6), Cells(4, 255)).Clear
.Range("A6").Value = "Agency"
.Range("B6").Value = "Server"
.Range("C6").Value = "Policy"
.Range("D6").Value = "Description"
.Rows("6:6").Font.Bold = True

ReportDays = ReportCriteria.PickFinishDate -
ReportCriteria.PickStartDate

'Write the Dates for the Grid Column Headings
ReDim DateArray(ReportDays + 1)

For i = 0 To ReportDays
.Cells(6, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(6, 6 + i).NumberFormat = "dd mmm"
.Cells(6, 6 + i).HorizontalAlignment = xlCenter

.Cells(5, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(5, 6 + i).NumberFormat = "ddd"
.Cells(5, 6 + i).HorizontalAlignment = xlCenter
DateArray(i + 1) = FormatDateTime(ReportCriteria.PickStartDate + i,
vbShortDate)
Next i

.Range("F4").AutoFill Destination:=.Range(Cells(4, 5), Cells(4,
12)), Type:=xlFillDefault
End With

--

Dave Peterson

Well spotted. I'll fix that up. Thanks for the proof-read.

(These things happen when you teach yourself) :-)

I think you're solution needs to have those cells() qualified, too.

Greg Glynn wrote:

Well spotted. I'll fix that up. Thanks for the proof-read.

(These things happen when you teach yourself) :-)

--

Dave Peterson