Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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.

Using .AutoFill with a CELLS() reference
 

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

Using .AutoFill with a CELLS() reference
 

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

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

Using .AutoFill with a CELLS() reference
 

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